Cross-Referencing numbers. Need help please

M

Mike

Hi

Hope someone can help. I have two sets of numbers that I would like to
cross reference with each other. They are parcel numbers. The first column
is the old style. The second column holds the new style. I would like to
create a form that if you input one or the other of the values it's counter
part would show up. An example below:

1559-F-1 PT SW 65-4-120-053-0510
1559-F-2 PT SW 65-4-120-053-0520
1560-F-1 E 1/2 65-4-120-054-0101
1560-F-1 PT SE 65-4-120-054-0120
1560-F-1 PT SE 65-4-120-054-0130
1560-F-2-A LOT 65-4-120-054-0200
1560-F-2-B LOT 65-4-120-054-0210
1560-F-3 PT SE 65-4-120-054-0220
1560-F PT SE 1/ 65-4-120-054-0230
1562-F PT SE 1/ 65-4-120-054-0410


Say if I entered the old parcel number of the first row as "1559-F-1" I
would like it's equivalent "65-4-120-053-0510" to show up. Or switch it
around and type in the new parcel number of "65-4-120-053-0510" and the old
number of "1559-F-1" should appear.

There are also three basic numbers that I will be using.

65-4-120-XXX-XXXX
66-4-120-XXX-XXXX
67-4-120-XXX-XXXX

Is there a way to just type in the "X" values and not have to use the whole
number or even need to type the dashes but yet still have then show up.

I have never used this site before and I may be asking for way to much. If
so please just ignore my request. I thought this would or should be fairly
simple and volunteered to do it, but now find myself scratching my head
after several hours at the attempt.

Thanks in advance for any help.

Mike
 
M

Mike

I forgot to mention that I am using MS Excel 2002. I also have MS Access if
this problem would be better solved there.
 
B

Biff

Hi!

Where does one number end and the other begin?

1559-F-1 PT SW 65-4-120-053-0510
the old parcel number of the first row as "1559-F-1"
Click to expand...
the new parcel number of "65-4-120-053-0510"
Click to expand...

Where is "PT SW" ? Is it part of the old style number? Part of the new style
number?

Biff
 
M

Mike

Sorry, I had them separated but when I posted they ran together for some
reason.

The correct second column of numbers would be in the below sequence with 13
numbers.

65-4-120-053-0510

The first column would be the preceding numbers in front such as 1559-F-1 PT
SW. These could very in length but the 2nd column is consistant with the 13
numbers and dashes.
 
M

Mike

Just a little more clarification. Their are over 5000 entries in each of
the two columns. Below is the same samples with underscores to show where
one column ends and the other begins. I would be more than happy to send the
whole excel file if that would help. It's size is: 825 KB (845,312 bytes)

1559-F-1 PT SW_______ 65-4-120-053-0510
1559-F-2 PT SW_______ 65-4-120-053-0520
1560-F-1 E 1/2________ 65-4-120-054-0101
1560-F-1 PT SE_______ 65-4-120-054-0120
1560-F-1 PT SE_______ 65-4-120-054-0130
1560-F-2-A LOT_______ 65-4-120-054-0200
1560-F-2-B LOT ______65-4-120-054-0210
1560-F-3 PT SE_______ 65-4-120-054-0220
1560-F PT SE 1/_______ 65-4-120-054-0230
1562-F PT SE 1/_______ 65-4-120-054-0410

Thanks again to anyone who offers a solution.
 
B

Biff

Hi!

OK, to answer your basic request:

Assume you enter either the new style or old style ID number in cell A1.
The range of the ID numbers is A3:B5000. Enter this formula in cell B1:

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000,MATCH(A1,B3:B5000,0)),VLOOKUP(A1,A3:B5000,2,0))

Biff
 
M

Mike

I can't believe it. That works slick. Looking at that formula I would have
been here till next year scratching my head. Thank you, Thank you.
 
G

Guest

Biff's equation is nice.

for the second part of your question
for the three basic numbers would there be duplicates in the XXX-XXXX portion?
if no,
you could type in XXX-XXXX and change Biff's equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000,MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&A1,A3:A5000,0))

to get rid of the last Hyphen you could enter "XXXXXXX"
and change the equation to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000,MATCH(A1,B3:B5000,0)),index(B3:B5000,match("*"&LeftA1,3)&"-"&right(A1,4),A3:A5000,0))

If there are duplicates the equation is more complex but you could enter
YXXXXXXX
where Y is the 5 6 or 7
and further change the formula to

=IF(ISNA(VLOOKUP(A1,A3:B5000,2,0)),INDEX(A3:A5000,MATCH(A1,B3:B5000,0)),index(B3:B5000,match("6"&left(A1,1)&"-4-120-"&mid(A1,2,3)&"-"&right(A1,4),A3:A5000,0))

neddless to say try them out on a copy of your file. I am notorius for typos.
 
M

Mike

Can't believe all the great help I'm getting here. My first experience with
a newsgroup and I can't say enough good about it. I won't get a chance to
try this out till late this eve, but will sure do so then. Thanks. I'll let
you know what happens.
 
M

Mike

Sorry, I think my brain is fried. Can't seem to make any of these work.
Here is what I have now. I changed some of the numbers in the formula to
actual cell ranges that are being used. I may have messed it up.

I am inputting numbers into cell A1. A text explaination in cell B1
A text explaination in cell A2 and the below formula into cell B2.
My range of column numbers start in cell A3 with 65-4-120-011-0101
Column B3 starts with 8-F ALL THAT PT

Here is the formula that I am trying. I've checked it over several times but
that doesn't mean anything.
This is the formula you suggested as:
" to get rid of the last Hyphen you could enter "XXXXXXX" and change the
equation to"


=IF(ISNA(VLOOKUP(A1,A3:B5949,2,0)),INDEX(A3:A5949,MATCH(A1,B3:B5949,0)),INDEX(B3:B5949,MATCH("*"&LeftA1,3)&"-"&right(A1,4),A3:A5949,0))

As before I can't thank you enough. If you would rather not deal with this
it's OK because the other fix from Biff will work fine. I don't want to wear
out my welcome here. I think this forum will be an invaluable help for me.

Below are the actual numbers used in columns A and B for the first 20 rows.

Mike

Column A
65-4-120-011-0101
65-4-120-011-0105
65-4-120-011-0120
65-4-120-011-0205
65-4-120-011-0210
65-4-120-011-0300
65-4-120-011-0310
65-4-120-011-0320
65-4-120-011-0330
65-4-120-011-0340

65-4-120-011-0350
65-4-120-011-0360
65-4-120-011-0370
65-4-120-011-0380
65-4-120-011-0390
65-4-120-011-0400
65-4-120-011-0410
65-4-120-011-0420
65-4-120-011-0430
65-4-120-011-0440


Column B
8-F ALL THAT PT
8-F PT OF NE 1
3-F-1 PT NE 1/4
3-F W 1/2 NE 1/
3-F CSM #2106 D
9-F-1 PT E 1/2
9-F-3-P PT E 1/
9-F-3-D PT NE 1
9-F-3-E PT NE 1
9-F-3-H PT E 1/
9-F-3-O PT E 1/
9-F-3-N PT E 1/
9-F-2 PT NE 1/4
9-F-3-M PT NE 1
9-F-3-L PT NE 1
9-F-3-K PT NE 1
9-F-3-J PT NE 1
9-F-3-Q PT NE 1
9-F-3-G PT E 1/
9-F-3-B PT NE 1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup help please 2
Formula help 1
Finding a number in a table? 10
Index & match/IF/Vlookup formula help 5
Need help with frequency formula 2
Time punch data from rows to columns 3
Count in a range of numbers 8
Tough teaser! 10

Top