Comparing 2 columns of numbers for differences

S

silverlucky5

New Member
Joined
Sep 8, 2009
Messages
35
Hi,
I have two lists of numbers that are supposed to be identical but are not.
Some numbers are in col 1 and not in col 2.
Some numbers are in Col 2 and not in Col 1. (see sample).
With my sample, since it is very small, it is easy enough to compare the two columns to see the differences.
But the columns I am actually comparing are thousands of rows long (and unlike the sample below, in my actual data, the "matches" are not aligned.).

Is there any way to write a macro to do this compare and tell me which numbers are in col 1 and not in col 2 and which numbers are in col 2 and not in col 1?

Thanks for any help!

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" height="17" width="64">col 1</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">col 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">1234</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">1234</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">5678</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">5678</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">10122</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">10122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">14566</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">14566</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">19010</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">19010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">23454</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">27898</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">32342</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">32342</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">36786</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">36786</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">41230</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">41230</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">45674</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">45674</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">50118</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">54562</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">54562</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">59006</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">59006</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">63450</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">63450</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">67894</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">67894</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">81226</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">81226</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">85670</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">85670</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">76782</td> </tr> </tbody></table>
 
Sort by date Sort by votes
Hi, In the interest of wanting to get thing right, Try this:-
The correct Result was in the array "Ray", but I had declared enough room in the final range for the Results.
In new code, your data assumed to be in columns "A & B" and starting Row (2).
Results columns as before :-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG16Apr54
'[COLOR="Green"][B]Forum[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] col = 1 To 2
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(2, col), Cells(rows.Count, col).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
      [COLOR="Navy"]If[/COLOR] Dn.value <> "" And Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.value, "Column " & col
         [COLOR="Navy"]Else[/COLOR]
             [COLOR="Navy"]If[/COLOR] Not .Item(Dn.value) = "Column " & col [COLOR="Navy"]Then[/COLOR]
                .Remove Dn.value
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR] col
Range("E1").Resize(.Count, 2) = Application.Transpose(Array(.keys, .Items))
[COLOR="Navy"]End[/COLOR] With
'[COLOR="Green"][B]====================[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Rng.Resize(Rng.Count * 2)
ReDim Ray(1 To rows.Count, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
      [COLOR="Navy"]If[/COLOR] Dn.value <> "" [COLOR="Navy"]Then[/COLOR]
         [COLOR="Navy"]For[/COLOR] col = 1 To 2
            oVal = IIf(col = 1, Dn, Dn.Offset(, 1))
            [COLOR="Navy"]If[/COLOR] Not .Exists(oVal) [COLOR="Navy"]Then[/COLOR]
                n = n + 1
                num = n
                .Add oVal, Array(num, col, 0)
                Ray(n, col) = oVal
            [COLOR="Navy"]Else[/COLOR]
                Q = .Item(oVal)
                [COLOR="Navy"]If[/COLOR] Not Q(1) = col [COLOR="Navy"]Then[/COLOR]
                    Ray(Q(0), col) = oVal
                [COLOR="Navy"]Else[/COLOR]
                    n = n + 1
                       [COLOR="Navy"]If[/COLOR] Q(2) = 0 [COLOR="Navy"]Then[/COLOR]
                            Ray(n, col) = oVal
                            Q(0) = Q(0) & "," & n
                            Q(2) = Q(2) + 1
                      oMax = Application.Max(Q(2), oMax)
                      [COLOR="Navy"]Else[/COLOR]
                            Ray(n, Split(Q(0))(Q(2))) = oVal
                      [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Range("C1").Resize(.Count + oMax, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Here's another variation. The numbers must be a contiguous block starting in "A1" & "B1" for this sample. The numbers can be listed as shown or sorted from lowest to highest.

Hope it helps.

Gary

Code: 
Public Sub Test()
 
Dim oLeft As Range
Dim oRight As Range
 
Set oLeft = ActiveSheet.Range("A1")
Set oRight = ActiveSheet.Range("B1")
 
Do Until oLeft.Value = "" Or oRight.Value = ""
    Select Case oLeft.Value - oRight.Value
    
        Case Is = 0
            Set oLeft = oLeft.Offset(1, 0)
            Set oRight = oRight.Offset(1, 0)
        Case Is > 0
            oLeft.Insert shift:=xlShiftDown
            Set oRight = oLeft.Offset(0, 1)
        Case Is < 0
            oRight.Insert shift:=xlShiftDown
            Set oLeft = oRight.Offset(0, -1)
    
    End Select
    
Loop
 
End Sub
 
Upvote 0
MickG said:
Hi, In the interest of wanting to get thing right, Try this:-
The correct Result was in the array "Ray", but I had declared enough room in the final range for the Results.
In new code, your data assumed to be in columns "A & B" and starting Row (2).
Results columns as before :-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG16Apr54
'[COLOR="Green"][B]Forum[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] col = 1 To 2
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(2, col), Cells(rows.Count, col).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
      [COLOR="Navy"]If[/COLOR] Dn.value <> "" And Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.value, "Column " & col
         [COLOR="Navy"]Else[/COLOR]
             [COLOR="Navy"]If[/COLOR] Not .Item(Dn.value) = "Column " & col [COLOR="Navy"]Then[/COLOR]
                .Remove Dn.value
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR] col
Range("E1").Resize(.Count, 2) = Application.Transpose(Array(.keys, .Items))
[COLOR="Navy"]End[/COLOR] With
'[COLOR="Green"][B]====================[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Rng.Resize(Rng.Count * 2)
ReDim Ray(1 To rows.Count, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
      [COLOR="Navy"]If[/COLOR] Dn.value <> "" [COLOR="Navy"]Then[/COLOR]
         [COLOR="Navy"]For[/COLOR] col = 1 To 2
            oVal = IIf(col = 1, Dn, Dn.Offset(, 1))
            [COLOR="Navy"]If[/COLOR] Not .Exists(oVal) [COLOR="Navy"]Then[/COLOR]
                n = n + 1
                num = n
                .Add oVal, Array(num, col, 0)
                Ray(n, col) = oVal
            [COLOR="Navy"]Else[/COLOR]
                Q = .Item(oVal)
                [COLOR="Navy"]If[/COLOR] Not Q(1) = col [COLOR="Navy"]Then[/COLOR]
                    Ray(Q(0), col) = oVal
                [COLOR="Navy"]Else[/COLOR]
                    n = n + 1
                       [COLOR="Navy"]If[/COLOR] Q(2) = 0 [COLOR="Navy"]Then[/COLOR]
                            Ray(n, col) = oVal
                            Q(0) = Q(0) & "," & n
                            Q(2) = Q(2) + 1
                      oMax = Application.Max(Q(2), oMax)
                      [COLOR="Navy"]Else[/COLOR]
                            Ray(n, Split(Q(0))(Q(2))) = oVal
                      [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Range("C1").Resize(.Count + oMax, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
Click to expand...
Hi Mick and Gary, That's it! Both your solution work! I am new to VBA so I am studying both diligently to make sure I understand how they work. And now, because I have no shame, if the 2 columns are not just numbers, but a mix of both numerics and alphanumerics (see sample below), could the code be accommodated to allow for that situation? I am trying to figure out how to alter either solution myself, but at the rate I'm going, it will be Dec 2012 before I figure it out and according to the Mayans, I then won't care. lol If you are feeling generous and patient and feel like giving that a shot, I would be very grateful. Thanks!

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" width="64"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" height="17" width="64">col 1</td> <td class="xl22" style="width: 48pt;" width="64">col2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">a1</td> <td class="xl22">a1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">24d</td> <td class="xl22">24e</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">24e</td> <td class="xl22">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">3</td> <td class="xl22">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">4</td> <td class="xl22">5ui</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">5ui</td> <td class="xl22">6</td> </tr> </tbody></table>
 
Upvote 0
The following seems to work for the numbers and the alpha numerics. I can't be sure it will work every time given such a tiny sample.

Hope it helps.

Gary

Code: 
Public Sub Test2()
 
Dim oLeft As Range
Dim oRight As Range
 
Set oLeft = ActiveSheet.Range("A1")
Set oRight = ActiveSheet.Range("B1")
 
Do Until oLeft.Value = "" Or oRight.Value = ""
 
If oLeft.Value = oRight.Value Then
    Set oLeft = oLeft.Offset(1, 0)
    Set oRight = oRight.Offset(1, 0)
ElseIf oLeft.Value > oRight.Value Then
    oLeft.Insert shift:=xlShiftDown
    Set oRight = oLeft.Offset(0, 1)
Else
    oRight.Insert shift:=xlShiftDown
    Set oLeft = oRight.Offset(0, -1)
End If
    
Loop
 
End Sub
 
Upvote 0
The Data Aligning part (2nd bit) of previous code of mine is not correct.
Try the Two new Data Aligining codes below, both seem to work for more comprehensive data and check out against each other.
Code (1) Counts the individual Values per column, sets in an Array then into sheet.
Code (2) Assigns indiviual values to a Jagged array then on to sheet.
Code(1) Results Start Column "E"
Code: 
[COLOR=navy]Sub[/COLOR] MG17Apr47
'[COLOR=green][B]Count Type[/B][/COLOR]
[COLOR=navy]Dim[/COLOR] RngA [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] oval, omax
[COLOR=navy]Dim[/COLOR] RngB [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] col [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] ColA, ColB
[COLOR=navy]Dim[/COLOR] Q
[COLOR=navy]Set[/COLOR] RngA = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
    [COLOR=navy]Set[/COLOR] RngB = Range(Range("B2"), Range("B" & rows.Count).End(xlUp))
        omax = Application.Max(RngA.Count, RngB.Count)
            [COLOR=navy]Set[/COLOR] RngA = Range("A2").Resize(omax)
                ReDim ray(1 To RngA.Count * 2, 1 To 2)
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] RngA
            [COLOR=navy]For[/COLOR] col = 1 To 2
                oval = IIf(col = 1, Dn, Dn.Offset(, 1))
                [COLOR=navy]If[/COLOR] Not .Exists(oval) [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]If[/COLOR] col = 1 [COLOR=navy]Then[/COLOR]
                        ColA = 1: ColB = 0
                    [COLOR=navy]Else[/COLOR]
                        ColB = 1: ColA = 0
                    [COLOR=navy]End[/COLOR] If
                    .Add oval, Array(ColA, ColB, 1)
                [COLOR=navy]Else[/COLOR]
                    Q = .Item(oval)
                    [COLOR=navy]If[/COLOR] col = 1 [COLOR=navy]Then[/COLOR]
                        Q(0) = Q(0) + 1
                    [COLOR=navy]ElseIf[/COLOR] col = 2 [COLOR=navy]Then[/COLOR]
                        Q(1) = Q(1) + 1
                    [COLOR=navy]End[/COLOR] If
                    Q(2) = Application.Max(Q(0), Q(1))
                    .Item(oval) = Q
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR] col
        [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Dim[/COLOR] rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Bc [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
    [COLOR=navy]For[/COLOR] n = 1 To .Item(K)(2)
        rw = rw + 1
        Ac = IIf(n > .Item(K)(0), "", K)
        Bc = IIf(n > .Item(K)(1), "", K)
        ray(rw, 1) = Ac
        ray(rw, 2) = Bc
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] K
Range("E1").Resize(rw, 2) = ray
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Code(2) Results Start column "C"
Code: 
[COLOR=navy]Sub[/COLOR] MG17Apr01
'[COLOR=green][B][Array Type[/B][/COLOR]
[COLOR=navy]Dim[/COLOR] RngA [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] oval, omax
[COLOR=navy]Dim[/COLOR] RngB [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] col [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] CA, CB
[COLOR=navy]Dim[/COLOR] Q
[COLOR=navy]Set[/COLOR] RngA = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
  [COLOR=navy]Set[/COLOR] RngB = Range(Range("B2"), Range("B" & rows.Count).End(xlUp))
    omax = Application.Max(RngA.Count, RngB.Count)
        [COLOR=navy]Set[/COLOR] RngA = Range("A2").Resize(omax)
            ReDim ray(1 To RngA.Count * 2, 1 To 2) '[COLOR=green][B]Final Array[/B][/COLOR]
                ReDim nRay(1 To RngA.Count * 10) '[COLOR=green][B]Vertical Array (Jagged)[/B][/COLOR]
                    ReDim RwRay(1 To RngA.Count * 10, 1 To 2) '[COLOR=green][B]Horizontal Array (Jagged)[/B][/COLOR]
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] RngA
    For col = 1 To 2 '[COLOR=green][B] Loop across RngA[/B][/COLOR]
        oval = IIf(col = 1, Dn, Dn.Offset(, 1))
            [COLOR=navy]If[/COLOR] Not .Exists(oval) [COLOR=navy]Then[/COLOR]
                n = n + 1
                '[COLOR=green][B]Redim Horizontal array for each Vert Array Index[/B][/COLOR]
                ReDim RayAc(1 To RngA.Count, 1 To 2)
                nRay(n) = RayAc
                nRay(n)(1, col) = oval
                [COLOR=navy]If[/COLOR] col = 1 [COLOR=navy]Then[/COLOR] CA = 1: CB = 0
                [COLOR=navy]If[/COLOR] col = 2 [COLOR=navy]Then[/COLOR] CB = 1: CA = 0
                    .Add oval, Array(nRay(n), CA, CB, 1)
            [COLOR=navy]Else[/COLOR]
                Q = .Item(oval)
                    [COLOR=navy]If[/COLOR] col = 1 [COLOR=navy]Then[/COLOR]
                        Q(1) = Q(1) + 1
                        Q(0)(Q(1), col) = oval
                    [COLOR=navy]ElseIf[/COLOR] col = 2 [COLOR=navy]Then[/COLOR]
                        Q(2) = Q(2) + 1
                        Q(0)(Q(2), col) = oval
                    [COLOR=navy]End[/COLOR] If
                Q(3) = Application.Max(Q(1), Q(2))
                .Item(oval) = Q
            [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] col
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Dim[/COLOR] K [COLOR=navy]As[/COLOR] Variant, rw [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
For Each K In .keys '[COLOR=green][B]Each individual Value in keys[/B][/COLOR]
    For n = 1 To .Item(K)(3) '[COLOR=green][B]Max Size for each Horiz Array[/B][/COLOR]
        rw = rw + 1
        '[COLOR=green][B]NB:- .item(K)(0) = the array nRay(n)[/B][/COLOR]
        RwRay(rw, 1) = .Item(K)(0)(n, 1)
        RwRay(rw, 2) = .Item(K)(0)(n, 2)
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] K
Range("C1").Resize(rw, 2) = RwRay
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> <!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
And great success for both Mick's 2 solutions and Gary's solution!!!

What I am actually doing is comparing data from two files of 20,000+ rows each. Each file concatenates approx. 20 columns of various types of alpha/alphanumeric/numeric/special character data into 1 column and then I compare the concatenated column from the first file to the concatenated column from the second file.

I tried all code out on the actual 20,000+ rows files and all 3 solutions work in an instant! And I have to do this compare for 15 "sets" of files of approximately the same size. As you can imagine, you both have saved me MORE THAN TREMENDOUS amounts of time. I can't thank you both enough for your help... If I ever win the lottery, I'll come back on this message board and find you both to share the wealth! :) Thanks again so much...
 
Upvote 0
I am using code 2 of MickG above and was wondering if its possible to pull along with the results, data in adjacent columns on both sides of the columns that are being compared.
Thank you
 
Upvote 0
MickG said:
Please show an example of your data with your expected result.
Click to expand...
I am comparing/matching columns B & F . Columns A,C & D go together with B. Column E goes together with F. Example of data:
ABCDEF
CREDIT
ACH CREDIT2015070111500560
ACH CREDIT83*1591031071\
ACH CREDIT FAC 303 1501
PRIVATEDEPOSIT FAC 50 1502
ACH CREDIT
ACH CREDIT 0903436092*1223267318\
ACH CREDIT94*1411245*000026\
<colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead> </thead><tbody> [TD="align: center"]1[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]9964.78[/TD] [TD="align: right"]1161369760[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]232.76[/TD] [TD="align: center"]2[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]232.76[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]581.46[/TD] [TD="align: center"]3[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]3101.85[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]787.50[/TD] [TD="align: center"]4[/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]581.46[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]3101.85[/TD] [TD="align: center"]5[/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]787.50[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]3515.43[/TD] [TD="align: center"]6[/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]3515.43[/TD] [TD="align: right"][/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]9964.78[/TD] [TD="align: center"]7[/TD] [TD="align: right"]7/3/2015[/TD] [TD="align: right"]11290.00[/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]1978.00[/TD] [TD="align: center"]8[/TD] [TD="align: right"]7/4/2015[/TD] [TD="align: right"]3101.85[/TD] [TD="align: right"]7/3/2015[/TD] [TD="align: right"]3101.85[/TD] </tbody>
​
result:
HIJKLM
CREDIT
ACH CREDIT2015070111500560
ACH CREDIT83*1591031071\
ACH CREDIT FAC 303 1501
PRIVATEDEPOSIT FAC 50 1502
ACH CREDIT
ACH CREDIT 0903436092*1223267318\
ACH CREDIT94*1411245*000026\
<colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead> </thead><tbody> [TD="align: center"]1[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]9964.78[/TD] [TD="align: right"]1161369760[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]9964.78[/TD] [TD="align: center"]2[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]232.76[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]232.76[/TD] [TD="align: center"]3[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]3101.85[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]3101.85[/TD] [TD="align: center"]4[/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]581.46[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]581.46[/TD] [TD="align: center"]5[/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]787.50[/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]787.50[/TD] [TD="align: center"]6[/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]3515.43[/TD] [TD="align: right"][/TD] [TD="align: right"]7/1/2015[/TD] [TD="align: right"]3515.43[/TD] [TD="align: center"]7[/TD] [TD="align: right"]7/3/2015[/TD] [TD="align: right"]11290.00[/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: center"]8[/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"][/TD] [TD="align: right"]7/2/2015[/TD] [TD="align: right"]1978.00[/TD] [TD="align: center"]9[/TD] [TD="align: right"]7/4/2015[/TD] [TD="align: right"]3101.85[/TD] [TD="align: right"]7/3/2015[/TD] [TD="align: right"]3101.85[/TD] </tbody>
​
 
Upvote 0
Hopefully this will do what you want !!!
Results start "H1"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG12Nov44
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
Ray = ActiveSheet.Range("A1").CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] Rw = 1 To UBound(Ray, 1)
        .Item(Ray(Rw, 2)) = Rw
    [COLOR="Navy"]Next[/COLOR] Rw


[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("F1"), Range("F" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Ray(.Item(Dn.Value), 6) = Dn.Value
        Ray(.Item(Dn.Value), 5) = Dn.Offset(, -1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
Range("H1").Resize(UBound(Ray, 1), 6) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
691
fjns
F

Forum statistics

Threads
1,224,586
Messages
6,179,719
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top