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!
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!