How to Compare 2 Lists using Excel VBA(4/4)

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

How to Compare 2 Lists using Excel VBA

This video is the 4th in the Excel VBA Dictionary series.

In this video we look at another real-world example that can be solved using the dictionary.

The task is to compare 2 lists of data and to return one of three results:
1. All items in list 1 only.
2. All items in list 2 only.
3. All items both lists.

In video I show you how to design a simple application to provides the result you specify. It full of proven techniques used by professional programmers.

Useful VBA Shortcut Keys:
Shift + F2: Get the definition of the item under the cursor.
Ctrl + Shift + F2: Go to the last cursor position.
Ctrl + Space: AutoComplete Word.
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Explorer Window.
F4: View the Properties Window.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent).
Shift + Tab: To move lines of code to the left(Outdent).

[nirman]

Really appreciable you teaching methods .
Thankyou

[nelseify]

Was struggling with a problem, looping through a task collection in microsoft project. Saw this and gave me the idea to add them to a dictionary so I could use the exists method on my custom ID number. Worked a charm. Thanks a million

[davidunger]

Paul, thank you so much for showing us how to use the VBA Dictionary. I have now applied these methods in my projects to produce unique lists from data containing duplicate items. Until now I have used the Advanced filter, and that works fine with medium size lists, but it really starts to bog down with large lists. Dictionary is much faster, here's my results comparing Advanced filter vs Dictionary, applied to a 16, 500 item list : Advanced filter: 1.0+ sec, Dictionary: 0.05 sec. No comparison! Thanks again.

[Excelmacromastery]

This is the 4th video in the Dictionary series. You can find the source code in the description of the video.

Please add any comments/queries below.


-Paul

[philb]

I couldn't see the point in using a dictionary until I saw your 4 part series. I always used arrays resulting in more complicated code. Now I'm going to practice practice practice dictionaries until I get it right.

Thank you for your clear easy to understand videos

[benterrell]

I guess, for this specific problem, you could also use power query and an inner join with remove duplicates. Ten seconds of work. But I still love the video. Keep them coming.

[thecodingmentor]

I was just working on this problem today and now I see your video! Thats awesome, thanks si much for your quality content

[robertbateman]

Thanks for the great video! It really helped me understand dictionaries and their uses. I made your code a bit more dynamic and added push buttons to help me "see the light!" I implemented your instructions for my boss to use. He loves it. You made me look like a genius! Thanks again!

[ricardoevandropereiradeoli]

Excelente material. O Brasil acompanha os seu conteúdo, muito obrigado pela didática no ensino.

[thearchibaldtuttle]

Very useful, thank you! What I generally like is the approach of using VBA and not trying to achieve this with some crazy formula. Would probably be possible but hard to maintain etc.

[someonenakai]

Thank you, excellent and it helped greatly to solve my data issue.

[rrrprogram]

I am yet to watch the previous videos about this seriess.... So i am gonna come to this later...

Thanks paul

[tommy]

Excellent video as always. The mscorlib.dll reference leads me to believe that an I'll be looking forward to it!!

[tfox]

Hi Paul,


The +1 after the LBound keeps it from picking up the first item in the Dictionary when doing the matching. I believe it needs to be removed to work correctly. But great video and thanks for all your help!!

[vuho]

Can you make a video using array, please? And if you can give the pro, con and time to run. Thank you for great video

[jasonbrinkman]

I just came across this code and it was very helpful. Without changing any of your code from the download, I cannot get "A1" to show up in the result list. The B list works great. Another commenter below found the same issue. I am struggling to locate in the code why it will not recognize cell "A2" when it is different from the rest? Thoughts?

[andresdelgado]

Hello. Thanks for the video. Is there a way to get the unique elements from both lists. Thank you.

[valentinbunualeslacruz]

Hello!
I have downloaded your excel file and there is a mistake.
When I chose the option "List1Only" VBA find "A2", "A3" & "A4" . "A1" must be.
Could you check it and try to repair the code, please?

By the other hand I like so much your videos...you are a good teacher!!!

[HappyAnimalsD]

This is off the topic but I have a quiz app where I have 2 or 4 choices against each mcq. How would I code my form to show 2 or 4 radio buttons for choices at runtime while looping through my mcqs?

[vijaysahal]

hii sir plz make a fresh blog on a compair two list but sir plz make with simple arrey nd loop