Counting Duplicate Files and comparing to list

[apo]

Hi..

I am trying to make a Macro that will search through a folder on my PC and look for file names with a string such as shown below:

The issue i am facing is that many of the files have duplicates.

I need it to check to see if the number of duplicates in Column E for each file name have the same amount of duplicate files in the actual folder... and if they don't I need the rows(s) highlighted Yellow.

Example: Say I run this macro.. and there are only 2 files in the folder that start with "BH1003".. as there is 3 instances in column E.. the 3 rows that have "BH1003" in them should all highlight..


I have added my code so far below also, but it only highlights each row Yellow if it finds an instance of the string in column E.. I don't know how to get it to count and compare....

Private Sub CommandButton3_Click()

'Search Folder for files - Highlight Yellow if Exists - Highlight Red if NOT Exists.
   Dim r As Long, lastRow As Long
      
   With ThisWorkbook.ActiveSheet
      'get last row
      lastRow = .Cells(Rows.Count, "E").End(xlUp).Row
             
          
      'cycle until last row
      For r = 6 To lastRow
                  
         If Dir(.Cells(r, "E")) <> "" Then
         Rows(r).Interior.ColorIndex = 6
         
         End If
      Next r
   End With


End Sub

Column E

D:\Files\BH1001*

D:\Files\BH1002*

D:\Files\BH1003*

D:\Files\BH1003*

D:\Files\BH1003*

D:\Files\BH1004*

D:\Files\BH1004*

D:\Files\BH1005*

D:\Files\BH1007*

D:\Files\BH1008*

D:\Files\BH1008* Thanks for any help..

<tbody>
</tbody>

 

[wigi]

Hello

Try the untested code below, which will fill column F with how many times the item in column E (same row) was found:

Private Sub CommandButton3_Click()

    Dim r As Long, lastRow As Long


    lastRow = .Cells(Rows.Count, "E").End(xlUp).Row


    c00 = "D:\Files\#*"


    'cycle until last row
    For r = 6 To lastRow


        If Len(.Cells(r, "E")) Then


            c01 = Dir(Replace(c00, "#", .Cells(r, "E")))
            j = 1
            Do Until c01 = ""
                j = j + 1
                c01 = Dir
            Loop

            .Cells(r, "F") = j

        End If
    Next

End Sub

For example, column E should countain BH1004.

 

[apo]

Hi..

Thanks fro your help..

I am getting the following error:

runtime error '52' bad filename or number

It errors at this line:

c01 = Dir(Replace(c00, "#", .Cells(r, "E")))

Any idea what is wrong?

Thanks

 

[apo]

Hi again..

I found the issue for the error was I had D:\Files\BH1004* in column E instead of just BH1004..

I have added only 1 file to the folder that is being searched.. the file is BH1003

The issue now is that it returns a value of 1 for every row except the 2 rows that have BH1003 in them. It is returning a value of 2 for each of those rows.

I am not sure what's going on there...

 

[wigi]

Oh, small mistake. j = 1 should read j = 0.