Highlight Partial Duplicates in Columns (w/a Twist)

[BunnyD]

I need to compare (2) columns , in Excel, and highlight the unique cells -- BUT -- performing a simple name comparison will NOT work. I need to find a way, in VBA and/or Conditional Formatting, to find partial name similarities and ignore those as being unique.

The following examples are considered the same file name (they are NOT unique):

107130-02.dwg
107130-02-Pre.dwg

300-202035-01.dwg
300-202035-01-Pre.dwg

311-410131-01.dwg
311-410131-01-Pre.dwg

340-560335-01.dwg
340-560335-01.dwg - Shortcut.lnk

360-561035-02.dwg
360-561035-02-Pre - A.dwg

360-561035-01.dwg
360-561035-01-Pre-B-.dwg
​

The following examples are considered unique:

100141-04.dwg
100735-12.dwg
108035-02.dwg
200870-01.dwg
312-100835-04.dwg
360-203230-07.dwg
602675-01.dwg​

In my document, I'm using Column $B$5:$B$1048575 and Column $C$5:$C$1048575. Column C is where I want the highlights to occur.

The columns are AutoCAD files queried from two separate folders on a network with 600+ files each. This task needs to be performed multiple times in a year. Multiple users generate these files and file naming standardization is not practical. Any ideas based on my criteria?

 

[shknbk2]

I'm not sure what data you have in Col B and what data is in Col C, but just using the data as shown below, the lengthy formula in my Col C can be used as a Conditional Formatting rule.

The formula starts with everything to the left of the first period. Then, if there is a "-Pre", it uses the text before it. Then, it adds back in the ".dwg" to hopefully come up with its duplicate if found.

Book1
	A	B	C
1	107130-02.dwg	107130-02-Pre.dwg	1
2	300-202035-01.dwg	300-202035-01-Pre.dwg	1
3	311-410131-01.dwg	311-410131-01-Pre.dwg	1
4	340-560335-01.dwg	340-560335-01.dwg - Shortcut.lnk	1
5	360-561035-01.dwg	360-561035-01-Pre-B-.dwg	1
6	360-561035-02.dwg	360-561035-02-Pre - A.dwg	1
Sheet1

Cell Formulas
Range	Formula
C1	=COUNTIF(A:A,"="&CONCAT(LEFT(LEFT(B1,FIND(".",B1)-1),IFERROR(FIND("-Pre",LEFT(B1,FIND(".",B1)-1))-1,250)),".dwg"))

 

[BunnyD]

Thank you, shknbk2

Although I tried to make sure all of my info was included... I missed including "what data is in the columns".
Column B has a query of all the files in Location 1.
Column C has a query of all the files in Location 2.
The current query has ~560 files in Location 1, ~375 in Location 2.

Wow. This formula works! I'm going to run some tests but on the surface this looks like it solves my issues!!

Thank you very much, shknbk2

 

[shknbk2]

You're welcome. Keep me posted in case it doesn't provide the best solution after you run the tests.

 

[Peter_SSs]

Thread title says "Highlight Partial Duplicates"
Post 1 says "highlight the unique cells"
These seem contradictory to me.

I've gone with the thread title, so see if this also does the job for your Conditional Formatting.

Excel Workbook
	A	B
1	107130-02.dwg	107130-02-Pre.dwg
2	300-202035-01.dwg	602675-01.dwg
3	311-410131-01.dwg	300-202035-01-Pre.dwg
4	340-560335-01.dwg	311-410131-01-Pre.dwg
5	360-561035-01.dwg	312-100835-04.dwg
6	360-561035-02.dwg	340-560335-01.dwg - Shortcut.lnk
7		360-561035-01-Pre-B-.dwg
8		366-561035-02-Pre - A.dwg
9
Partial Dupes

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =MATCH(LEFT(B1,SEARCH("-Pre",SUBSTITUTE(B1,".","-Pre"))-1)&".dwg",A$1:A$1000,0)	Abc

 

[BunnyD]

Hi Peter_SSs,

Thank you for your input; the formula that shknbk2 provided works beautifully.... sorry for the title/description confusion.
This is going to help my team save a ton of time!

 

[Peter_SSs]

No problem - just offering alternatives and of course you should choose what suits you best.

BTW, "Welcome to the MrExcel board!"