Verify Data from Two Workbooks to Match Dupes and Number of Instances

[learningVBA321]

Hello everyone,

I know that this can be done with countif, index, vlookup, sumif, conditional formatting, etc. However, they are tedious when looking at thousands of entries. I can simply use conditional formatting to call out differences, but the issue I am trying to address goes a step further. I am trying to create a macro that will let me match up two columns not just for entries, but also for the number of instancesof said entries all at once.

So, say I have two columns, as the attachment shows. I want to indicate first what does and does not match up. Done easily enough with a simple conditional formatting and highlighting. So we easily see that Sample3 and Sample 4 do not match, so there is an issue there.

The next part, though, is that we want to verify that the same number of each item appears in each column. So, we need to show that Sample shows up 5 times in List1 and then 5 times in List2. The countif gets us that, but assume there are thousands of items (there are) and they usually only repeat up to 5 times. So we need to see if an item shows up more or less in each column, relative to each other.

Can anyone tell me the quickest way/code to do this? I am trying different options on my own, but the people on here are smarter than I am!

I tried to attach the below as a sheet, but I do not see an option to do that.

[TABLE="width: 382"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]List 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]List 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample
[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample1[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]Sample1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample2[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample1[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample1[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample4[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample2[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample2[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample2[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample2[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Sample2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Sample2[/TD]
[TD="align: right"]14[/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[MickG]

Try this for results in columns "G" to "N".

[COLOR="Navy"]Sub[/COLOR] MG24Aug36
[COLOR="Navy"]Dim[/COLOR] rng [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 [COLOR="Navy"]As[/COLOR] Variant, cc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ccc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cccc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] List1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] List2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng1 = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
    Range("G:J").Clear
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
 oVal = Array(rng, Rng1)
[COLOR="Navy"]For[/COLOR] n = 0 To 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] oVal(n)
        List1 = 0: List2 = 0
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] n = 0 [COLOR="Navy"]Then[/COLOR] List1 = 1 Else List2 = 1
            .Add Dn.Value, Array(List1, List2)
        [COLOR="Navy"]Else[/COLOR]
            Q = .item(Dn.Value)
                [COLOR="Navy"]If[/COLOR] n = 0 [COLOR="Navy"]Then[/COLOR] Q(0) = Q(0) + 1 Else Q(1) = Q(1) + 1
            .item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] n
c = 1: cc = 1: ccc = 1: cccc = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
        [COLOR="Navy"]Case[/COLOR] .item(K)(0) = .item(K)(1)
            c = c + 1
         Cells(c, "G") = K
         Cells(c, "H") = .item(K)(0)
        [COLOR="Navy"]Case[/COLOR] .item(K)(0) = 0
            ccc = ccc + 1
            Cells(ccc, "K") = K
            Cells(ccc, "L") = .item(K)(1)
        [COLOR="Navy"]Case[/COLOR] .item(K)(1) = 0
            cccc = cccc + 1
            Cells(cccc, "M") = K
            Cells(cccc, "N") = .item(K)(0)
        [COLOR="Navy"]Case[/COLOR] .item(K)(0) <> .item(K)(1)
            cc = cc + 1
            Cells(cc, "I") = K
            Cells(cc, "J") = .item(K)(0) & ", " & .item(K)(1)
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
oMax = Application.Max(c, cc, ccc, cccc)
[COLOR="Navy"]With[/COLOR] Range("G1:n1")
   .Value = Array("Equal Matches", "EqNum", "Unequal Matches", "UnEqNum (L1/L2)", "Not Fd in List1", "NotFdNumL1", "Not Fd in List2", " NotFdNumL2")
   [COLOR="Navy"]With[/COLOR] .Resize(oMax)
        .Borders.Weight = 2
        .Columns.AutoFit
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[learningVBA321]

Wow, wow, wow! You took my thoughts another step further again! I must say I wonder what you do, perhaps a software coder? You seem to be able to just whip this stuff up on a whim, very impressive. Your code worked perfectly and I am working it into a macro that I have set up to run to pull in other data, formatting, and so on. I am awaiting feedback on all of this, but I had to come back and say thank you so much again MickG! I am going to try to do that post thanks thing, see what that does on here.

I do wish I could understand everything you did, like the For Each K and next Dn stuff along with the cccc pieces, etc. Like I said, I tried to mess around with it but the debugger hated it. You may hear from me again, I hope that is okay. Again, I cannot thank you enough.

 

[learningVBA321]

Dupe

 

[MickG]

Thank you for your enthusiastic appreciation.
Regrds Mick