Cross referencing

[Harley78]

Morning all, I am looking for help on a way to figure out if one plant has the same part but is labeled differently.

Example,

We stock the #123456789 Tire Bearing and our service plant stocks the X123456789 Tire Bearing. Same part one just begins with a # sign and the other with an X.

Is it possible to compare both inventories and have it tell me this one is the same? To me it's like a Vlookup, however, the first digit is an X which i don't think helps.

Is there a solution?

 

[VoG]

Perhaps a fuzzy lookup http://www.mrexcel.com/board2/viewtopic.php?p=974873#974873

 

[Andrew Poulsom]

Can't you just substitute the # for an X?

=COUNTIF(A:A,SUBSTITUTE("#123456789 Tire Bearing","#","X"))>0

You can use a cell reference in place of "#123456789 Tire Bearing".

 

[Harley78]

You guys are good,,,,,

But I forgot to expand,,,, that if I have a list of parts with # on one worksheet, how is it that I compare those with a X on another worksheet and then note that number on the first sheet?,,,, like i normally do with a Vlookup

 

[Andrew Poulsom]

Use SUBSTITUTE like I did in your VLOOKUP formula.

 

[Harley78]

Thanks a lot Andrew, I understand your reply, however, ignorant on how to combine them...

Usually I have

=VLOOKUP(b:b,'WORKSHEET2'!A:B,2,FALSE)

Of course this finds both part numbers and enters the quantity in the other plant.

I am just ignorant how to combine your reply

 

[Andrew Poulsom]

More like:

=VLOOKUP(SUBSTITUTE(B1,"#","X"),'WORKSHEET2'!A:B,2,FALSE)

 

[Harley78]

i knew it,, i just have placement issues...lol. is there a way that I would know how to create that formula or is it a trial and error thing?

Oh, and thank you very much for the formula... i just wish i can figure these out without having to bother people.