Match partial duplicates in two columns

Ebippity

New Member
Joined
Mar 1, 2019
Messages
6
I'm trying to find partial dupes comparing data in two columns and limiting the matches to a specified number of digits.

Ridiculously Simple Example: If column 1 has "1234" and column 2 has "3456" (and these are formatted as text, not numbers), it would match those because they both "34" in them. I would be able to specify that at least 2 consecutive digits have to match.

Here is a sample of the data I'm working with. How do I do this?

First ColumnSecond ColumnMatch Column
VV GJ-900-0003492BLAHMATCH
BLAH GJ-900-0003493 BLAHBLAHMATCH
MP-002-00214MANUAL PAYMENTSNO MATCH
MP-900-00101MANUAL PAYMENTSNO MATCH
VGJ-900-00083VOID GJ-900-0003492MATCH
VGJ-900-00085VOID GJ-900-0003493MATCH
GJ-003-0001374write off year-old GPNMATCH
GJ-003-0001517write off year-old GPNNO MATCH
VGJ-003-00089VOID GJ-003-0001374MATCH
GJ-003-0001374write off year-old GPNMATCH
GJ-003-0001517write off year-old GPNNO MATCH
VGJ-003-00089VOID GJ-003-0001374MATCH

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Thank you for any help on this!!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Ebippity

New Member
Joined
Mar 1, 2019
Messages
6
Oh except I made a mistake. The third row from the bottom of the second column is NOT a match.
 

Ebippity

New Member
Joined
Mar 1, 2019
Messages
6
Oh wait... it IS a match. Sheesh. Anyone know how to delete or edit your own posts? ;)
 

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
209
=IF(COUNTIF($A$2:$A$100,"*"&B2&"*")+SUMPRODUCt(COUNTIF(B2,"*"&$A$2:$A$100&"*")),"MATCH","")
 

Ebippity

New Member
Joined
Mar 1, 2019
Messages
6
=IF(COUNTIF($A$2:$A$100,"*"&B2&"*")+SUMPRODUCt(COUNTIF(B2,"*"&$A$2:$A$100&"*")),"MATCH","")

Phuoc, sorry for taking so long to respond. It's been crazy over here! Finally got a chance to test this out and I'm so excited that it's working. Thank you!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,467
Members
409,883
Latest member
asharris90
Top