0 votes
in VBA by Beginner (5 points)

I have 2 lists that I need to compare. List 1 may contain, for example: apples, oranges, plums, pears. List 2 may contain: apples, apples, oranges, plums, plums, pears. If list 1 should have the duplicate values (there should be 2 instances of apples and plums, not just 1), would the macro show list 1 as missing 1 instance of apples and 1 instance of plums?

Source: VBA Compare two lists and show missing values

1 Answer

+2 votes
by Skilled (419 points)
selected by
Best answer

Hi trrider53,

Great question! Before we get to the answer, we need to fix a minor typo in the posted code that you referenced:

' change the first occurrence of LastRow_1 to LastRow_2

.Value = Evaluate("IF(COUNTIF(" & Col_2 & StartRow & ":" & Col_2 & LastRow_2 & ", " & Col_1 & StartRow & ":" & Col_1 & LastRow_1 & "),""""," & Col_1 & StartRow & ":" & Col_1 & LastRow_1 & ")")

' This fix will allow list 2 to be longer than list one.

Now, no matter which list has the duplicates, the macro will not output the instances. COUNTIF can't tell one apple from another. More accurately, the macro is designed to detect TRUE or FALSE, even though it's obscurred by the output.

IF(logical test,OUTPUT if TRUE, OUTPUT if FALSE)

The logical test really evaluates to  either a non-zero value, which is TRUE, or a value of zero, which is FALSE. That's why the COUNTIF function can detect missing fruit: it evaluates to zero for any fruit that is in List 1, but not List 2.

By the way, this means that you need a second test. That gets complex, really fast. Instead, you may want to look at the dictionary object. See the reference, below. On that page, you'll learn how to list unique items. This should point you in the right direction.




Excel VBA Dictionary Keys and Items


by Super Expert (3.2k points)
Thanks for pointing out that typo, Mitch! I'll get it fixed with the next publication!
by Skilled (419 points)
You're welcome, Ryan. I had a lot of fun with that macro!



Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.