Highlight Partial Duplicates in Columns (w/a Twist)

BunnyD

New Member
Joined
Sep 12, 2019
Messages
3
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?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
238
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">107130-02.dwg</td><td style=";">107130-02-Pre.dwg</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">300-202035-01.dwg</td><td style=";">300-202035-01-Pre.dwg</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">311-410131-01.dwg</td><td style=";">311-410131-01-Pre.dwg</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">340-560335-01.dwg</td><td style=";">340-560335-01.dwg - Shortcut.lnk</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">360-561035-01.dwg</td><td style=";">360-561035-01-Pre-B-.dwg</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">360-561035-02.dwg</td><td style=";">360-561035-02-Pre - A.dwg</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=COUNTIF(<font color="Blue">A:A,"="&CONCAT(<font color="Red">LEFT(<font color="Green">LEFT(<font color="Purple">B1,FIND(<font color="Teal">".",B1</font>)-1</font>),IFERROR(<font color="Purple">FIND(<font color="Teal">"-Pre",LEFT(<font color="#FF00FF">B1,FIND(<font color="Navy">".",B1</font>)-1</font>)</font>)-1,250</font>)</font>),".dwg"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

BunnyD

New Member
Joined
Sep 12, 2019
Messages
3
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

Board Regular
Joined
Mar 5, 2016
Messages
238
You're welcome. Keep me posted in case it doesn't provide the best solution after you run the tests.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,404
Office Version
365
Platform
Windows
Thread title says "Highlight Partial Duplicates"
Post 1 says "highlight the unique cells"
These seem contradictory to me. :confused:

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

Excel Workbook
AB
1107130-02.dwg107130-02-Pre.dwg
2300-202035-01.dwg602675-01.dwg
3311-410131-01.dwg300-202035-01-Pre.dwg
4340-560335-01.dwg311-410131-01-Pre.dwg
5360-561035-01.dwg312-100835-04.dwg
6360-561035-02.dwg340-560335-01.dwg - Shortcut.lnk
7360-561035-01-Pre-B-.dwg
8366-561035-02-Pre - A.dwg
9
Partial Dupes
#VALUE!
 
Last edited:

BunnyD

New Member
Joined
Sep 12, 2019
Messages
3
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,404
Office Version
365
Platform
Windows
No problem - just offering alternatives and of course you should choose what suits you best. :)

BTW, "Welcome to the MrExcel board!"
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,198
Messages
5,442,985
Members
405,211
Latest member
Hamonta Lahon

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top