find duplicates in an array

[montecarlo2012]

Hello, I have a dynamic array 1 at (B1:F6845) and array 2 at (I6809:M6845), both are integers only; I would like to compare the array two against array one and highlight the duplicates in the array 2. and at the end of array two the amount of duplicates founded. like in N6846 the total of duplicates founded.


thanks.

 

[hiker95]

montecarlo2012,

but I really tried is to compare array1 (B2:to the end) against array2 , this second array is dynamic also, could be (I2: to the end) or could be just small as the example. in the same spat ofcourse. thanks.

That is not what you displayed in your workbook?

Be back in a little while.

 

[hiker95]

montecarlo2012,

The following macro has not been tested because I do not have your complete dataset.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub FindDuplicates_V2()
' hiker95, 12/22/2014, ME825417
Dim lrb As Long, lri As Long
Dim c As Range, g As Range, n As Long
Application.ScreenUpdating = False
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lri = Cells(Rows.Count, "I").End(xlUp).Row
With Range("G2:G" & lrb)
  .Formula = "=B2&C2&D2&E2&F2"
  .Value = .Value
End With
With Range("H2:H" & lri)
  .Formula = "=I2&J2&K2&L2&M2"
  .Value = .Value
End With
For Each c In Range("H2:H" & lri)
  Set g = Range("G2:G" & lrb).Find(c.Value, LookAt:=xlWhole)
  If Not g Is Nothing Then
    n = n + 1
    Range("I" & c.Row).Resize(, 5).Interior.Color = vbYellow
  End If
Next c
With Range("O2")
  .Value = n
  .Interior.Color = vbYellow
End With
Range("G2:G" & lrb).ClearContents
Range("H2:H" & lri).ClearContents
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindDuplicates_V2 macro.

 

[Peter_SSs]

The following macro has not been tested because I do not have your complete dataset.

.. but in any case it will have the downfall that I pointed out earlier. The OP has numbers like 1 and 11 in the cells. Your formulas will see 1 and 11 as "111" and will also see 11 and 1 as "111" when in fact the corresponding cells are not equal. Or have I missed the requirement?

 

[hiker95]

Peter_SSs,

.. but in any case it will have the downfall that I pointed out earlier. The OP has numbers like 1 and 11 in the cells. Your formulas will see 1 and 11 as "111" and will also see 11 and 1 as "111" when in fact the corresponding cells are not equal. Or have I missed the requirement?

Nice catch - thanks.

 

[hiker95]

montecarlo2012,

The following macro has been adjusted based on what Peter_SSs found - thanks again Peter_SSs.

I have changed the formulae in columns G, and, H, so that the numbers will be concatenated with the , character.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub FindDuplicates_V3()
' hiker95, 12/23/2014, ME825417
Dim lrb As Long, lri As Long
Dim c As Range, g As Range, n As Long
Application.ScreenUpdating = False
lrb = Cells(Rows.Count, "B").End(xlUp).Row
lri = Cells(Rows.Count, "I").End(xlUp).Row
With Range("G2:G" & lrb)
  .Formula = "=B2&"",""&C2&"",""&D2&"",""&E2&"",""&F2"
  .Value = .Value
End With
With Range("H2:H" & lri)
  .Formula = "=I2&"",""&J2&"",""&K2&"",""&L2&"",""&M2"
  .Value = .Value
End With
For Each c In Range("H2:H" & lri)
  Set g = Range("G2:G" & lrb).Find(c.Value, LookAt:=xlWhole)
  If Not g Is Nothing Then
    n = n + 1
    Range("I" & c.Row).Resize(, 5).Interior.Color = vbYellow
  End If
Next c
With Range("O2")
  .Value = n
  .Interior.Color = vbYellow
End With
Range("G2:G" & lrb).ClearContents
Range("H2:H" & lri).ClearContents
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindDuplicates_V3 macro.

 

[montecarlo2012]

Thank you guys, work soo good, we don't have people like you here.

 

[hiker95]

montecarlo2012,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.