Almost Duplicates

[samantham]

I just started a new job, and am working on a project for my boss. I ran DupFinder, and found that we have over 6,000 duplicate files on our department drive. I exported the DupFinder results into Excel.

Some of these files were saved into different folders for some reason. One copy might be in H:\Recruiting, another in H:\Reports\Recruiting, and another in H:\Admin\Recruiting. Each file has the same name, but due to staff turnover, they may have different "last modified" dates.

Rather than going through the file line by line by line, is there a way I can have Excel find these files that have the same name but different modification dates, and highlight them? I'm going nuts doing this by hand and it's only been 40 minutes.

Thank you!

 

[Warship]

Is most likely doable - complexity depends on the exported data.
I'm not familiar with DupFinder and what its export includes.
Post a few rows of your worksheet...

 

[samantham]

Here are a few rows, with a same name/different date example included. Column C (hidden from view) just lists the file type (doc, docx, xls, xlsx, wpd).

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Path</td><td style=";">FileName</td><td style=";">Size</td><td style=";">DateTime</td></tr><tr ><td style="color: #161120;text-align: center;">93</td><td style="background-color: #FFFF00;;">H:\Recruitment\Classified Ads\CLASSADS\ACADEMIC\</td><td style="background-color: #FFFF00;;">Acad Affairs Inst Research & Effect Officer. Elkhart Truth 2011.docx</td><td style="text-align: right;background-color: #FFFF00;;">14910</td><td style="text-align: right;background-color: #FFFF00;;">2/15/2011 10:15</td></tr><tr ><td style="color: #161120;text-align: center;">94</td><td style="background-color: #FFFF00;;">H:\Reports\Recruitment\Classified Ads\CLASSADS\ACADEMIC\</td><td style="background-color: #FFFF00;;">Acad Affairs Inst Research & Effect Officer. Elkhart Truth 2011.docx</td><td style="text-align: right;background-color: #FFFF00;;">14889</td><td style="text-align: right;background-color: #FFFF00;;">1/19/2011 15:59</td></tr><tr ><td style="color: #161120;text-align: center;">95</td><td style=";">H:\Recruitment\Postings by Dept\Academic Affairs\</td><td style=";">Acad affairs secretary AD 091605.doc</td><td style="text-align: right;;">20480</td><td style="text-align: right;;">9/16/2005 10:54</td></tr><tr ><td style="color: #161120;text-align: center;">96</td><td style=";">H:\Reports\Recruitment\Posting\Academic Affairs\</td><td style=";">Acad affairs secretary AD 091605.doc</td><td style="text-align: right;;">20480</td><td style="text-align: right;;">9/16/2005 10:54</td></tr><tr ><td style="color: #161120;text-align: center;">97</td><td style=";">H:\Recruitment\Postings by Dept\Academic Affairs\</td><td style=";">Acad affairs secretary081205.doc</td><td style="text-align: right;;">22528</td><td style="text-align: right;;">8/12/2005 16:45</td></tr><tr ><td style="color: #161120;text-align: center;">98</td><td style=";">H:\Reports\Recruitment\Posting\Academic Affairs\</td><td style=";">Acad affairs secretary081205.doc</td><td style="text-align: right;;">22528</td><td style="text-align: right;;">8/12/2005 16:45</td></tr></tbody></table><p style="width:18.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Highlighted Sheet (without WPD)</p><br /><br />

 

[Warship]

This will take a while and there's probably a faster way. But I think it'll do what you're looking for.

Sub HiliteAlmostDupes()
  Dim fileDATA As Range, found As Range, foundFirst
  Dim r As Long, c As Long
  Application.ScreenUpdating = False
  Set fileDATA = Cells(1, 1).CurrentRegion
  For r = 2 To fileDATA.Rows.Count
    Application.StatusBar = "Row: " & r
    If fileDATA.Cells(r, 2).Interior.Color <> 65535 Then
        Set found = fileDATA.Columns(2).Find(fileDATA.Cells(r, 2), , , xlWhole)
        If Not found Is Nothing Then
          foundFirst = found.Address
          Do
            If Cells(r, 5) <> found.Offset(, 3) Then
              For c = 1 To 5
                Cells(r, c).Interior.Color = 65535
                Cells(found.Row, c).Interior.Color = 65535
              Next c
            End If
            Set found = fileDATA.FindNext(found)
          Loop While Not found Is Nothing And found.Address <> foundFirst
        End If
    End If
  Next r
End Sub

 

[samantham]

Thank you so much! This worked wonderfully! It didn't take long, and made things so much easier. I really appreciate your help!

Samantha

 

[Warship]

Glad it worked. You're quite welcome.
Guess I need a faster PC.

 

[samantham]

Considering that this computer crashes when I have Outlook and three Excel files open, I don't think my computer is much faster than yours. Could be it seemed faster than it was as I was multitasking and not looking at the clock!