How to match "close" values between two data sources?

Hello all - I have a document with a lot of items to match up - assuming they are matchable - and little time in which to do it.

is there a formula / function to identify and match those part numbers provided by data source #1 that are close to those provided by data source #2? Example of close = all characters are the same except one has dashes & one does not, one has spaces & the other has dashes, or another type of "difference" like that.

I have attached a document that shows the data with which I am working. I would appreciate any help that could be offered, the quicker, the better!! Thanks in advance.

I use Excel 2007 and my OS is WIndows XP. I know, I know...

Thanks, JBG

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

Your thread has been moved to the "General" forum.

One way, with two Helper columns

1. D6: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6,".",""),"-","")," ",""),"(",""),")","")

2. E6: =COUNTIF($D$6:$D$1182,$D6)


Copy both formulae down and filter on column E

HTML Code: 

Regards

@ConnexionLost: working on solution while you were moderating ;-) Apologies

Yes, you can add another SUBSTITUTE if you wish.

You'd probably need another function to remove the leading zero, assuming there is only one.

=IF(LEFT(B6,1)=0,RIGHT(B6,LEN(B6)-1),B6) rather than just B6 in the original formula.

Regards

You're welcome. Glad I could help.