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.

List 1			List 2
Sample	16		Sample	10
Sample1	5		Sample1	3
Sample2	6		Sample2	14
Sample	16		Sample	10
Sample	16		Sample	10
Sample	16		Sample	10
Sample	16		Sample	10
Sample	16		Sample	10
Sample	16		Sample	10
Sample	16		Sample	10
Sample	16		Sample	10
Sample	16		Sample	10
Sample1	5		Sample	10
Sample1	5		Sample	10
Sample4	0		Sample	10
Sample2	6		Sample	10
Sample2	6		Sample	10
Sample2	6		Sample	10
Sample2	6		Sample3	0
Sample2	6		Sample1	3
Sample2	6		Sample1	3
Sample2	6		Sample1	3
Sample2	6		Sample1	3
Sample2	6		Sample2	14
Sample2	6		Sample2	14
Sample2	6		Sample2	14
Sample2	6		Sample2	14
Sample2	6		Sample2	14

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

Thanks!

 

[MickG]

I assumed "List1" is in column "A" and "List2" is in column "C" with the results starting in "G1".

[COLOR=navy]Sub[/COLOR] MG19Aug40
[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] R [COLOR=navy]As[/COLOR] Range, oVal [COLOR=navy]As[/COLOR] Variant
[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] List1 [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] List2 [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
 oVal = Array(Rng, Rng.Offset(, 3))
[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]
            If n = 0 Then 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
Range("G1:J1").Value = Array("List1", "Fd in List2", "List2", "Fd in List1")
c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] .keys
    c = c + 1
    Cells(c, "G") = K
    Cells(c, "H") = .Item(K)(1)
    Cells(c, "I") = K
    Cells(c, "J") = .Item(K)(0)
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[MickG]

NB:- List2 is actually assumed to be in Column "D" not column "C", and your data assumed to start row 2. !!

 

[learningVBA321]

Wow, you are a genius, I am trying to go through the code and actually understand it. I do not get the first 2 lines with all the Dim stuff, the variants, etc. Your code does work though, but it seems as though it also sorts in some way? Like, I notice that if they are in list 2 but not in list one, they are moved down the list, to start again alphabetically. What part of the code made it do that? I was thinking it would be helpful to have it do that for both lists. For example, in my sheet, I have some items in list 1 that do not show up in list 2, but those are not set up in their own space like the others I just mentioned do.

Thank you so much, the knowledge you guys have is crazy. Like, I think there are maybe 250 people on earth who ACTUALLY know this type of coding, and the rest of us are just on the net searching for smarter people! Thanks again!

 

[MickG]

Thank you for your response
The code Selects the unique data in the order that it comes in list1 then list2, it does not do any sorting.
Regrds Mick

 

[learningVBA321]

Thanks, so is there a way to use the array to move each section of no matches to the next column? So there would be three columns...one with matches, one for list1 no match in list 2, then one for list2 no match in list1. I have been trying to do it on my own, but your code is so far above my head I am failing miserably!

Thanks again so much!

 

[MickG]

When you say "No match" do you mean that the count of the samples in each list are not equal or the sample name is not in the other column.
Depending on the above, how would you like to see the results.!!!!

 

[learningVBA321]

Oh man, good question, yikes, did not even think of that, and it is MY sheet! I think perhaps where the sample name is not in the other column. So it would have 3 arrays as noted above. So I would be calling out which ones were off in each list if there were simply no matches at all going either way.

However, your thought is better and more in-depth than what I was thinking....is it possible to have it both ways....so an array that has matches that are also the same count (that is the perfect list, no errors). Then an array that has matches, but different counts in each list (I think that is how your first example is now). Then an array that has list1 no sample name showing in list2. Then an array that has list 2 no sample name showing in list 1. Is that even possible? I may be asking too much!!

Thank you thank you either way!! =)

 

[MickG]

Try this for Results in columns "G to J".

[COLOR="Navy"]Sub[/COLOR] MG22Aug07
[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
        [COLOR="Navy"]Case[/COLOR] .Item(K)(0) = 0
            ccc = ccc + 1
            Cells(ccc, "I") = K
        [COLOR="Navy"]Case[/COLOR] .Item(K)(1) = 0
            cccc = cccc + 1
            Cells(cccc, "J") = K
        [COLOR="Navy"]Case[/COLOR] .Item(K)(0) <> .Item(K)(1)
            cc = cc + 1
            Cells(cc, "H") = K
    [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:J1")
   .Value = Array("Equal Matches", "Unequal Matches", "Not Fd in List1", "Not Fd in List2")
   [COLOR="Navy"]With[/COLOR] .Resize(oMax)
        .Borders.Weight = 2
        .Columns.AutoFit
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[learningVBA321]

Hi Mick, once again, thanks so much, and your code is flawless! Works perfectly. I did not reply yesterday because I was trying to add back in the piece you had that did the counting, as my people liked that function as well. Sadly, my skills are not able to match yours and I am not sure which section to add back in to your second bit of code. Could you please let me know what I have to do to add that bit back in? So it would show all that you have done in the latest code, but would add in the counting columns as well. I think probably following the 'Unequal Matches' column? There would be two more columns, one showing number found in list1, number found in list 2. I apologize for having to ask, I did try! If you just want to point out what pieces I need, I can try again to do it on my own!

Thank you again!!!