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

L

learningVBA321

New Member
Joined
Jul 10, 2017
Messages
30
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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
I assumed "List1" is in column "A" and "List2" is in column "C" with the results starting in "G1".
Code: 
[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
 
Last edited:
Upvote 0
NB:- List2 is actually assumed to be in Column "D" not column "C", and your data assumed to start row 2. !!
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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.!!!!
 
Upvote 0
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!! =)
 
Last edited:
Upvote 0
Try this for Results in columns "G to J".
Code: 
[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
 
Upvote 0
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!!!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Send emails to group leaders about fund used by their members:
Replies
0
Views
328
JojoGurkha
J
F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
188
Peter_SSs
Peter_SSs
J
  • Question Question
Conditional Formating Help
Replies
4
Views
362
Alex Blakenburg
A
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
360
Asbestos_Jen
Asbestos_Jen
R
  • Solved
VBA: Merging rows after a particular row, based off of matching entries in column
2
Replies
19
Views
956
Georgiboy
Georgiboy

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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