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

[@nirman484]

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

[@Excelmacromastery]

Glad to help.

[@davidunger7425]

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.

[@rb-today]

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!

[@philb2334]

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

[@Excelmacromastery]

You're welcome Phil.

[@benterrell9139]

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.

[@thecodingmentor7701]

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

[@Excelmacromastery]

Glad you like it.

[@CollDott]

Very useful!! So creative!!

[@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

[@anyname1337]

Hi Paul! I posted a question here on YT in the comments section. Do you have time to give it a look =)? Have a wonderful day - and thank you!

[@ricardoevandropereiradeoli9028]

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

[@Excelmacromastery]

Glad you like it Ricardo.

[@someonenakai9792]

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

[@tommy704581]

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

[@Excelmacromastery]

It's possible😀

[@tommy704581]

@@Excelmacromastery If you get a chance check out my post here: codereview.stackexchange.com/q/229762/171419. By Implementing mscorlib.IComparer and using an ArrayList my class sorts 2D arrays by multiple columns.

[@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.

[@rrrprogram8667]

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

[@72tfox]

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

[@davidunger7425]

You're correct, the +1 doesn't appear in the video.

[@jasonbrinkman52]

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?

[@vuho8975]

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

[@andresdelgado7766]

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

[@stephenkaras1523]

Very concise. Much like “WiseOwl.com”

[@valentinbunualeslacruz297]

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

[@karnabudhathoki5311]

I have learnt a lot about dictionary from this video. And I would suggest a slight modification in the logic.....The Second List is put in the Array and compared against Dictionary from list1.So it works well if items that are in both list is not repeated in the second list(second List is compared assigning it as an array). So While Comparing List1's Dictionary... against array(Second List), if there is a value that is on both list and is repeated in Second list). The first time that value is encountered ..it compares against the List1's Dictionary and removes the item from there .. So......So....So... The Second time the item that is repeated in the Second list is encountered...It compares against the List1's Dictionary...and Alas that item is already removed in the first encounter...So it thinks it is not in list 1 and adds to list2's Dictionary...So there Comes the error in logic...So I put the Second List items into a dictionary the same way as the first list and Passed it to CompareList and Used For Each loop ...And Now it's fine...working good....

[@bulldoghome]

Same logic fault I have noticed. So before comparing both lists have to be cleaned with unique items only.

[@mcochrane87]

Could you use a ‘Select Case’ in the function CompareLists , rather then the if, elseif, else?

[@Excelmacromastery]

Of course. Select Case and ElseIF are almost the same.

[@rb-today]

I find Select Case is faster IF there are a lot of choices (i.e. Months of the year). Otherwise they're pretty much the same.

[@houstonvanhoy7767]

Sir Paul: Items are added to a dictionary in random order, and not sorted before they are used in later steps. This is fine for small data sets. But if we deal with huge data sets, will that matter? Thanks.

[@Excelmacromastery]

I haven't done any benchmark testing so I can't say. If the data was huge it may be a issue.

[@Hey_Its_Colin]

Hi Paul, used this code and it was very helpful! Thank you for teaching this! Wondering if I could make it produce or print another result that is alongside the original print that shows both list1Only and list2Only side by side Any advice on how to code this?

[@HappyAnimals3D]

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?

[@vijaysahal4556]

sir I have some query can you help me for solve it bcz I can not done with it u hope 👍🏻

[@vijaysahal4556]

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

[@sgs9274]

Hi Paul, First of all, thank you for the videos, I've found them very helpful! Just wondering why you used dictionaries for this instead of arrays? Is it just to use .Exists() instead of looping through? And if we wanted to compare 2D tables to each other would this method work, or would we have to revert to arrays?

[@Excelmacromastery]

Yes, I used dictionaries because of Exists. If you use arrays you have to read through the second array to for each item in the first.

[@kennedyadams247]

How would you delete the cell (or row if lists are on separate sheets) if the value didn't show up in both lists?

[@strannostrannovasrr]

Very useful indeed. Thanks a lot. But how do you obtain the blue edged rectangulars with the values in it? Or is it just an animation?

[@thearchibaldtuttle]

This is a very neat animation!

[@Excelmacromastery]

It's animation😀😀

[@tangoc2]

Great tutorial. Nevertheless, when I apply your code into my worksheet. It doesn't work quite as expected. My worksheet is dates. So List 1 will be all dates format like dd/mm/yyyy and List 2 are public holiday as same format dd/mm/yyyy. The problem is when I try to show the result as List01only. The dd and mm will swap. So for example, if the date in list 1 is 9/1/2023, the first cell in result list will become 1/9/2023. But it didn't affect all dates. Only some of them will have this result. Any idea?

[@shaynebattjes1892]

The First value that belongs in “both” is retuned in “list2Only” - anyone know why?

[@Kumaranmoljha]

I have 2 lists of people's names. i want to Extract Names in List 1 that are NOT in List 2 data. i also want to highlight wildcard character , . for exampal paull king and paull kin i want to highlight paull kin in extracted list . please make a video in this sir

[@leomeijer5014]

Paul, your explanation of this subject was very good and for me not really hard to follow. I'm struggling a few years now trying to understand Dictionaries and Collections. So thank you very much for this tutorial. But... (always a "but" ;-) ). I'm trying to make a comparison of 2 tables with more fields than just 1 (like it is in this KZbin item). Is it also possible to put more fields in a Dict Value instead of just 1? What I mean, is I have a "on going report" with (let's say) 6 columns and 20 rows. Now I want to compare this with the "today's report" with ofcourse also 6 columns but now 25 rows. Now I want to delete the rows in the On Going version which are no longer in the Todays report. And on the other hand, I want to add records to the On Going version which are new in the Todays report. Is it doable with arrays (reading both reports in Arrays, comparing it with a Dictionary and afterward writing back the new table to the worksheet (after the the deletion of the "old" On Going version))? Is it possible for you to make a turorial for this specific matter? Or isn't this possible with a Dictionary/ Greetings from Delft, The Netherlands (and sorry if my written English is not so good)

[@Joker-mx8zs]

Leo Meijer Hi! I Have exactly the same issue here. Did you succeed with this matter?

[@thomashonea9164]

for some reason I am getting a Compile Error - variable not defined when I try to run the code even on the example.

[@Maddy130986]

I think this can be achieved by worksheet function countif also

[@Excelmacromastery]

Do you mean as a formula or with VBA code?

[@Maddy130986]

I learnt a lot from your videos. I really like all your videos. But I think, the below code can also be used The code may look like this For I= 2 to range("A1").currentregion.rows.count If worksheetfunction.countif(Range("A:A"), Range("B"& I).value)>0 then Range("B"&I).interior.color=vbYellow End if Next i Hope this code will satisfy the requirement.

[@Excelmacromastery]

@@Maddy130986 That code doesn't extract the data and it only checks for items that appear in both lists.

[@niravsavaliya823]

When to use Dim dict as Dictionary and when to use Dim dict as NEW Dictionary?

[@Excelmacromastery]

New creates the dictionary. "Dim d As Dictionary" creates the variable but it will be nothing. It needs to be assigned to an existing Dictionary or a new Dictionary before it can be used. Set d = New Dictionary Set d = existingDict

[@niravsavaliya823]

@Excelmacromastery Your videos are full of information. Really liked almost all of your videos.

[@williampaulson2246]

Excellent videos, but one issue, if in column B you had 2 of CC1 or CC2 the second CC1 or CC2 would show in ResultType : Both and dict2Only lists. This would happen because you perform the dict.remove the first time it finds it. The second time it compares CC1 or CC2 it would fail the .exists test and put it in dict2Only list. If anyone else has this problem my fix was to test for the item in the dictresult dictionary. If dict.Exists(item) = True Then dictresult(item) = 0 dict.Remove item End If If dictresult.Exists(item) = True Then Else dict2Only(item) = 0 ''''' Continue code from here'''' Again, thanks and love all your videos

[@sultanshaik9519]

In my project I have to capture range as image and save it in specified location. I was copied the range as xlScreen, xlBitmap format. And pasted into 'chartObject.chart'. But the image is blank. How do I fix. Please help me

[@elecirlisboa9155]

Ótimo conteúdo, parabéns, ganhou mais um inscrito, do Brasil

[@anthonymcgarry4306]

I am obviously doing something wrong here but when I use the code it wants me to declare the variables where as you don’t need to do this. For example it wants the shData defined. I’m very confused and it turns out multiple compile errors 😡 what have I done wrong

[@Excelmacromastery]

shData is the code name of the worksheet. See the code name explained here: bit.ly/3cCRW3e

[@Meolimo]

thks again ;-)

[@samnangsuonsokunth8719]

excuse me sir can you give me to download code

[@CollDott]

👌👌👏👏👍👍🥇

[@BiuroHintMine]

Hello :) Great example, but I'd like to ask what about SQL? Is SQL slower or less precise? I always do it like this: Sub sprawdzaczka() Dim pol Dim zap Dim sci sci = ThisWorkbook.FullName Set pol = CreateObject("ADODB.Connection") Set zap = CreateObject("ADODB.Recordset") pol.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sci & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" zap.Open "select ListB from [Data$] where ListB not in (select ListA from [Data$])", pol Sheets("Data").Range("D2").CopyFromRecordset zap pol.Close End Sub

[@DrunkenKnight71]

Thanks for the video, I copied the code and the data exactly like in your worksheet and the code works perfectly. One thing though, i copied the range of data A2:B9 and pasted it below, effectively making the range A2:B17. I changed the code to account for this new range and now the code is not working. When I ask it to do list2Only it spits out the Cs as well as the Bs.... 'list1Only' and 'both' behave as expected. Any ideas?

[@DrunkenKnight71]

it's ok, i worked it out...in the CompareLists function, I replaced 'Else' with 'ElseIf dictResult.Exists(item) = False Then'

[@Excelmacromastery]

Glad you got it sorted.

[@melheor4909]

Your dictionary videos starting from the 3-rd out of 4 are bad.

[@tughanozsezer9332]

.

[@SuperneroMy]

List-A JohnDoe 12345 HarryKriss 45687 FerryM 76543 List-B John Doe 12345 Felix Charm 543212 Alex Mann 6914 Harry Krisstoff 45687 Zach Duo 92453 Swen Page 41234 Kary Paul 1549 Ferry Mack 76543 The list is something like this in my case...Here, List-B is a master list In both, List-A and List-B: 1st column is Employee name and 2nd column is Employee code Where, I have to compare the code of an employee between List-A and List-B then, output which employee is missing in List A

[@SuperneroMy]

JohnDoe 12345 HarryKriss 45687 FerryM 76543 John Doe 12345 Felix Charm 543212 Alex Mann 6914 Harry Krisstoff 45687 Zach Duo 92453 Swen Page 41234 Kary Paul 1549 Ferry Mack 76543 The list is something like this in my case...Here, ListB is a master list In both, List A and List B: 1st column is Employee name and 2nd column is Employee code Where, I have to compare the code of an employee between ListA and ListB then, output which employee is missing in List A

[@Tecnical-Eng-PLC]

Hi Mrs can you please help me to speed up the next code please Sub suliman111111() Dim StartingTime As Single StartingTime = Timer With Application .Calculation = xlCalculationManual .ScreenUpdating = False .DisplayStatusBar = False .DisplayStatusBar = False End With Dim c1 As Integer Dim c2 As Integer Dim c3 As Integer Dim c4 As Integer Dim a As Integer Dim d As Integer Sheets("namenotexist").Range("a:c").Value = "" d = 0 c1 = Application.WorksheetFunction.CountA(Sheets("name_inter").Range("b:b")) c2 = Application.WorksheetFunction.CountA(Sheets("databasic").Range("b:b")) c4 = Application.WorksheetFunction.CountA(Sheets("namenotexist").Range("b:b")) For i = 1 To c1 nam = Application.WorksheetFunction.Trim(Sheets("name_inter").Range("b" & i).Value) Sheets("name_inter").Range("b" & i).Value = nam Next i For ii = 1 To c2 nam = Application.WorksheetFunction.Trim(Sheets("databasic").Range("b" & ii).Value) Sheets("databasic").Range("b" & ii).Value = nam Next ii ''''äÕ Çáì ÚãæÏ''''' For a = 1 To c1 Sheets("name_inter").Range("b" & a).TextToColumns Destination:=Sheets("name_inter").Range("c" & a), DataType:=xlDelimited, _ ConsecutiveDelimiter:=True, Space:=True Next a For aa = 1 To c2 Sheets("databasic").Range("b" & aa).TextToColumns Destination:=Sheets("databasic").Range("c" & aa), DataType:=xlDelimited, _ ConsecutiveDelimiter:=True, Space:=True Next aa '''''''ãÞÇÑäÉ ÇáÃÓãÇÁ''''''' For m = 1 To c1 For k = 1 To c2 nn = Sheets("name_inter").Range("A" & m).Value d = 0 c4 = Application.WorksheetFunction.CountA(Sheets("namenotexist").Range("b:b")) For j = 1 To nn If Sheets("name_inter").Cells(m, j + 2).Value = Sheets("databasic").Cells(k, j + 2).Value Then d = d + 1 If d >= Sheets("name_inter").Range("k11").Value Then Sheets("namenotexist").Range("a1").Offset(c4, 0).Value = d Sheets("namenotexist").Range("c1").Offset(c4, 0).Value = Sheets("databasic").Cells(k, 2).Value Sheets("namenotexist").Range("b1").Offset(c4, 0).Value = Sheets("name_inter").Cells(m, 2).Value End If End If Next j Next k Next m Sheets("name_inter").Range("c1:i" & c1).Value = "" Sheets("databasic").Range("c1:i" & c2).Value = "" Sheets("namenotexist").Activate With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True .DisplayStatusBar = True .DisplayStatusBar = True End With MsgBox Timer - StartingTime End Sub