• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

comparing two arrays and highlight duplicates

V

vicktor schausberger

Member
I have two arrays and would be easier to find duplicates using a vba code. I had been doing manually using different things like index match, countif, and, ifiserror but do not work my array are A1:F53 and the other is H1:M53 I need to compare ROW AGAINST ROW and highlight the duplicates.
Code: 
ARRAY ONE                            ARRAY TWO                   
13    16    17    40    42    44        05    12    28    40    41    47
01    12    22    44    46    52        09    19    34    38    39    45
10    13    35    36    37    42        05    14    29    41    45    51
09    26    34    40    42    49        04    17    30    44    47    52
01    02    25    43    48    53        11    23    29    41    48    51
08    32    35    46    47    52        14    16    22    38    45    47
14    15    23    36    49    50        09    21    29    38    42    44
15    17    22    40    41    45                           
03    26    36    37    43    44
 
Hi Vicktor ,

Can you clarify one point ?

To be a duplicate should the items in the second matrix be in the same order as the items in the first matrix ?

In the data you have posted , there are no duplicates , correct ?

Narayan
 
Why do you have to use two arrays? Can't you just use formula and conditional formatting to solve this one? I don't see this as a vba solution at all. Just test the line for a like number in the same row and colour me bad!!!

Take care

Smallman

Edit

Here is a workbook outlining my thoughts. Duplicates highlighted - no vba.
 

Attachments

  • CondFormatting.xlsx
    8.6 KB · Views: 15
Hi Narayan

I think he meant he needed to compare each row in each Array. That is the way I read it anyways. This is the only situation where there are duplicates. Need him to come back and clarify really.

Take care

Smallman
 
Hi, thanks for reading this.
-A --B --C- D- -E- --F - -- H --I-- J-- K-- L--- M
01 -02 -25 43 -48 53------ 11 -23 -29 41- 48 -51
this is the row A5:F5 if you compare against H5:M5 the cell E an L are duplicates
the next row
08 -32 -35 -46 -47 -52----- 14- 16- 22- 38 -45 -47 the first row compare against the H:M the cell E is
47 and the cell M is a duplicate. Sorry I didn't explained from the beginning.
 
That sounds about right to me. So you just have to extend the conditional formatting to both Ranges or Arrays ?as you put it. Does this cover that off? See attached.

Smallman
 

Attachments

  • CondFormatting.xlsx
    8.7 KB · Views: 19
​
not difficult by a VBA process (beginner level),​
but the difficulty is to understand the exact needs with partial explanation !​
  • Highlight duplicates : in both arrays or which one ?
  • Compare row against row : only with the same row number or whatever the row in the other array ?
  • First array has more rows than the second one, so don't compare the last two rows ?
  • Do you really need VBA 'cause with conditional formatting …
​
 
YES, with the same row number
those row in the post is just an example, the real arrays are iqual A1:F53 and H1:M53
YES, highlight both.
THANKS, and YES I really need a VBA code,
 
My code is not at beginner level as expected only to speed up the procedure ...​
Only the MATCH function is needed to find row duplicates :​
Code: 
Sub HighlightRowDuplicates(ByVal Rg1 As Range, ByVal Rg2 As Range)
    Dim RgI As Range
 
    If Rg1.Count = 1 Then Set Rg1 = Rg1.CurrentRegion
    If Rg2.Count = 1 Then Set Rg2 = Rg2.CurrentRegion
    Application.ScreenUpdating = False
    Union(Rg1, Rg2).Font.ColorIndex = 0
    Set RgI = Intersect(Rg1.EntireRow, Rg2.EntireRow)
 
    If Not RgI Is Nothing Then
        Set Rg1 = Intersect(RgI, Rg1)
        Set Rg2 = Intersect(RgI, Rg2)
        Set RgI = Nothing
 
        For R& = 1 To Rg1.Rows.Count
            AR = Application.Match(Rg1.Rows(R), Rg2.Rows(R), 0)
 
            For C& = 1 To UBound(AR)
                If Not IsError(AR(C)) Then _
                    Union(Rg1.Rows(R).Cells(C), Rg2.Rows(R).Cells(AR(C))).Font.ColorIndex = 3
            Next C
        Next R
    End If
 
    Application.ScreenUpdating = True
    Set Rg1 = Nothing: Set Rg2 = Nothing
End Sub
Like it !​
​
If a value of a row in first array exists twice in the same row of second array,​
only the first value in the second array is highlighted.​
In this case I have to go back to a beginner level code to highlight both …​
 
Thank you Marc L. in the array number two (right side), H1:M1, there are 3 duplicates; I am counting manually and typing in the column "O" this amounts, like "O1=1 O5=1 and O6 =1 this is vertical count, now I need horizontally I mean K9=1 ; L9=1 ; M9=1 and in column O9 the total 3, so would be so good if every time I run the code, I can see the partial totals vertically and horizontally and the main total in this little example in O9 , but you Know that our array is H1:M36 so the results in "O" 38.
 
My previous procedure controls if both arrays are in the same rows.​
​
This new one doesn't care about that, compares row by row by order in each array and updates totals :​
Code: 
Sub HighlightRowsDuplicates(ByVal Rg1 As Range, ByVal Rg2 As Range)
    If Rg1.Count = 1 Then Set Rg1 = Rg1.CurrentRegion
    If Rg2.Count = 1 Then Set Rg2 = Rg2.CurrentRegion
    RC& = Application.Min(Rg1.Rows.Count, Rg2.Rows.Count)
    SC& = Rg2.Columns.Count + 2
    ReDim HC(1 To SC)
    ReDim VC(1 To RC, 1 To 1)
    Application.ScreenUpdating = False
    Union(Rg1, Rg2).Font.ColorIndex = 0
 
    For R& = 1 To RC
        AR = Application.Match(Rg1.Rows(R), Rg2.Rows(R), 0)
 
        For C& = 1 To UBound(AR)
            If Not IsError(AR(C)) Then
                Union(Rg1.Rows(R).Cells(C), Rg2.Rows(R).Cells(AR(C))).Font.ColorIndex = 3
                HC(AR(C)) = HC(AR(C)) + 1
                 VC(R, 1) = VC(R, 1) + 1
                   HC(SC) = HC(SC) + 1
            End If
        Next C
    Next R
 
        Rg2.Cells(Rg2.Columns.Count)(1, 3).Resize(RC).Value = VC
    Rg2.Rows(Rg2.Rows.Count).Cells(3, 1).Resize(, SC).Value = HC
    Application.ScreenUpdating = True
    Set Rg1 = Nothing: Set Rg2 = Nothing
End Sub
If you like it, don't hesitate to click on Like in the bottom right of this message !​
 
@all
Hi!
"Sehen uns bald" stands for "See you soon".
Regards!

@vicktor schausberger
Hi!
When posting (short phrases, I hope) in other languages than English please be as kind as to provide immediately below the English translation.
Regards!
 
​
Hi SirJB7 !​
​
Carlsberg ! (no English translation 'cause it's a magic word well understood all over the world ‼)​
​
3d-irlandais-3.gif
 
You must log in or register to reply here.
Back
Top