Find duplicates [arrays]

[vicktor schausberger]

Hello everybody, Happy new year.
I am trying to find duplicates between two arrays.
my first array is at B2:F6700
my second array is at H2:L6700
I would like to compare the second array "H2:L6700" against the first.
both arrays are dynamics, but the array H:L can be small or as big as I described.
The output I am expecting is to know how many duplicates was founded
and this result in N2 will be completely find.
and in P2: T xend the founded duplicates, it is not about to highlight is to copy in the range P:T the numbers was found.

 

[Smallman]

Hi Vicktor

The fastest way to do it would be to concatenate B to F and H to L and put a countif fomrula in column N and then just use a filter to weed out the matches. Any 1 appearing in N is a match. This could be simply cut and pasted to the area you wish.

Example attached.

Take care

Smallman

 

[vicktor schausberger]

Smallman, thank you for your respond, what I really would like to see, is a ►CODE◄ you know better than me about this, one "click" and ♫bingo♫ my results. I really appreciate your answer. Happy new year.

 

[Marc L]

Hi Vicktor !

When you need a code, you must post your question
in the VBA Macros forum ! Here is the Excel formulas one …

Sub Demo()
    With Sheet1.[P2]
        Application.ScreenUpdating = False
        .CurrentRegion.Clear
        .Resize(.Offset(, -14).CurrentRegion.Rows.Count).FormulaR1C1 = "=RC[-14]&""¤""&RC[-13]&""¤""&RC[-12]&""¤""&RC[-11]&""¤""&RC[-10]"
        VFA = Application.Transpose(.CurrentRegion.Value)
        .CurrentRegion.Clear
        .Resize(.Offset(, -8).CurrentRegion.Rows.Count).FormulaR1C1 = "=RC[-8]&""¤""&RC[-7]&""¤""&RC[-6]&""¤""&RC[-5]&""¤""&RC[-4]"
        VSA = Application.Transpose(.CurrentRegion.Value)
        .CurrentRegion.Clear

        For Each VS In VSA
                           VR = Application.Match(VS, VFA, 0)
            If Not IsError(VR) Then
                .Offset(L&).Resize(, 5).Value = .Offset(VR - 1, -14).Resize(, 5).Value
                                            L = L + 1
            End If
        Next

        .Offset(, -2).Value = L
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

 

[vicktor schausberger]

Marc L, thank you so much, I click "►like◄" already, the best for the next year.