formula that uses the "find Logic"

[kwrcst]

Good morning everyone. I am a content specialist and build new items into our database. The problem we have is duplicate item builds. our products that we build have both a MFR number and a vendor number attached. sometimes they are the same, sometimes not. the program we use for this has a way of searching through all of our vendor numbers and it does a pretty good job but it doesn't look for MFR numbers. for this I pull the excel Item master. If it is 1-2 items, I can just use the "Find" to search but often its 10-150 items. sometimes when the items are added to the system, leading zeros are dropped or dashes and periods are removed. sometimes on purpose, others, not. I do 'Vlookup" now and if it is an exact match I will find it. I have ran it as "True" insted of "False" and I find it unreliable when dashes or periods are at play. I was wondering if there was a formula I can apply to a large number of items insted of lookiing them up 1 at a time? I know "Find" will pull the sequence out of a larger number but it will not if there is a dash seperateing them. I am attaching a sheet to emphasize my point.
The red MFR numbers are differentfor the example. I have left the formulas in place so you can see how they were set up.
Thanks for the help

Kelly

[Pete_UK]

I think your missing dots, dashes and leading zeros are in too irregular a position to be able to solve this problem with a formula. You can use wildcards with VLOOKUP but I don't think that will be possible in this situation. (I stand to be corrected, though !!)

Hope this helps.

Pete

[kwrcst]

unfortunatly, industry standards are not in place fot the dashes and dots. Can you explain wildcards for me? I am still newer to excel and do not understand all of its abilities

thanks

[Pete_UK]

Well, you could have a formula like:

=VLOOKUP(J2&"*",$G$2:$G$18,1,FALSE)

where J2 contains, say 40. This would then match with anything that began with 40. Similarly, you could have this:

=VLOOKUP("*"&J2&"*",$G$2:$G$18,1,FALSE)

which would then match with anything that contained 40, or you could have:

=VLOOKUP("*"&left(J2,1)&"*"&RIGHT(J2,1)&"*",$G$2:$G$18,1,FALSE)

which would match on anything containing a 4 and was followed by a zero in one of the other characters.

However, I don't think there is a generic solution to a single formula that could be copied down to find the appropriate matches.

Hope this helps.

Pete

[kwrcst]

how about his? is there a way to do a single vlookup that will search the entire page and not just a specific column?

[Pete_UK]

VLOOKUP always searches through the left-most column of the table that you define in the second parameter, so I'm not sure what you are getting at.

Pete

[kwrcst]

it gives me something to play with. Thanks

[kwrcst]

find will search the whole page. Is there a way for vlookup or any formula to search the whole page instead of just the specified column? That may be a better way of reststing the origional question since the dots and dashes throw it off.

[Pete_UK]

Yes, but you are trying to find a match between the items in column J and the other part of the sheet - what is the point in looking at the date column, or the description, or the manuf_code?

I suggest you do a Google search (or search on this forum) for "Fuzzy match" and see if you can get some help that way - as I said before, I don't think you'll be able to do this with a formula, but with a macro there are other ways of comparing values (like regular expressions) which are not available in the standard functions.

Hope this helps.

Pete

[kwrcst]

thanks. I will look that up. the reason I would want to look in other columns is, I found another way of pulling the database to include the vendor number and the MFR number on the same sheet. I thought it would be handy to have a 1 size fits all kind of lookup. I will see about a macro. thanks

[Pete_UK]

Well, if you have access to other data which has not been degraded or messed about, then it might be possible to use VLOOKUP, or MATCH - you would need some way of identifying and distinguishing between multiple duplicate entries, as those functions will stop searching at the first match they find.

Hope this helps.

Pete