Compare Two Columns Excel VBA Macro

Подписаться 6 тыс.

Просмотров 18 тыс.

50% 1

Excel Macro/VBA Code available in this post: excelmacroclass.blogspot.com/...
In a previous video we have seen how to compare two worksheets cell to cell. In this video we will see how to compare two columns with data in Excel using VBA macros. The macro compares the entries on each column in the active worksheet with respect to the other column and highlights the differences. It also adds the different values to another sheet for both columns. The example shows models of phones sold in 2020 vs 2022, and allows to see what is different between the two lists in Excel. The lists have unique entries, but the code can be used with multiple entries too. In that case, more items are highlighted,.. and repeated when printed to the other worksheet. But that can easily be adapted as needed. Find the code to compare two columns with data with Excel VBA macros in this post of the Excel Macro Class blog: excelmacroclass.blogspot.com/...
You can find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners) in the blog under the link: excelmacroclass.blogspot.com/
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 65

@michaelsmits8677 Год назад

I've listened to Excel gurus for hours with the only result that I got lost in a bunch of blabla. However, your method is easy to comprehend. It is fairly easy to integrate and expand the procedure in a larger whole. Great job, excellent teacher, thank you for the valuable lesson.

@ExcelMacroMania Год назад

Thanks for the feedback, truly appreciated!

@saaisundher 2 года назад

Good video. Straight to the point.

@ThongNguyen-vf4to 2 года назад

Thank for your time to guide.

@ytjegr 7 месяцев назад

Really thanks for this Video. i am very new to this Excel VBA. Today only i saw your video and came to know your blog. You are awesome.

@ExcelMacroMania 7 месяцев назад

Thanks for your words, glad to hear that!

@MohAboAbdo Год назад

Thank you so much

@francoisheydenrych6933 9 месяцев назад

Wow thank you very much

@tannertucker22 Год назад

Thank you. This is excellent. It would nice if it can compare numeric values.

@ExcelMacroMania Год назад

I replied to a similar comment below. The macro should also compare numbers, If that's not the case, it may be due to different formatting (General vs Number, for example), or decimals, or numbers as a result of a formula... Another thing you can try is to find a match against the whole of the search (instead of part) adding LookAt:=xlWhole to the Find method in the parenthesis and separated with a comma.

@tknaved 2 года назад

very good video, can you make a video, if value found in column C, then copy the row and paste to another sheet.

@ExcelMacroMania Год назад

I don't think is worth doing an entire video for that, because it's just a small tweak. You need to add something like this within the loop and inside the if statement "check if prod1 is in col2": Rows(r).Copy Sheets("difs").Cells(dif2, 2) Supposing you previously added the sheet "difs", and you are using dif2=dif2+1 to count the differences. This is based on the original code available here: excelmacroclass.blogspot.com/2022/04/compare-two-columns-with-data.html

@peterpaulignacio4950 Год назад

Very helpful video. How do you extract the discrepancies to other column?

@ExcelMacroMania Год назад

That's part of the macro. After highlighting the discrepancy in yellow, the next line increments dif1 and then writes to sheets "difs": dif1 = dif1 + 1 Sheets("difs").Cells(dif1, 1).Value = prod1 Full code is in this post: excelmacroclass.blogspot.com/2022/04/compare-two-columns-with-data.html

@tangoc2 Год назад

Very nice tutorial. Indeed, what if I want to extract in the same worksheet instead of others? For example in Column D?

@ExcelMacroMania Год назад

Replace the line that says: Sheets("difs").Cells(dif1, 1).Value = prod1 with: Cells(dif1, "D").Value = prod1 And do the same for product 2.

@luisam5060 6 месяцев назад

I really enjoyed this video, it is very helpful. I tried to practice with this example and at the start I was getting the desired results. However, after closing my excel spreadsheet and trying again with a new set of data, I am getting all my values highlighted. I haven't changed the code and I am getting different results. Is there a reason behind this ? I do not seem to understand what I did wrong. Would you have an idea of what could have possibly happened? Thank you!!!

@ExcelMacroMania 6 месяцев назад

So I assume you close 1 workbook but keep the workbook with the macro open, then you open workbook 2 and always run the macro from the workbook with data. The macro targets the active sheet in the active workbook, so if you run it with more workbooks open you need to make sure the one you want is active (what you see on the screen basically). If that's the case, I don't know why it wouldn't work a second time. Make sure you declare the variables, check the code in the blog here: excelmacroclass.blogspot.com/2022/04/compare-two-columns-with-data.html You could also set column1 and column2 to nothing at the end of the macro maybe. And ultimately, you can do is for example writing the code for a specific workbook or worksheet.

@WalterChiari000 Год назад

Hi, You can do the same thing without the use of a macro, with "Conditional Formatting". At least in Excel 2019.

@ExcelMacroMania Год назад

Indeed! Microsoft keeps adding functionality as most users do not have the time to learn/create macros. However, using macros gives you more flexibility to customize the tasks, and run it many times and instantly, etc, that's the point of using macros. So keep learning, you'll see it's worthy 🙂

@sumiths9284 Год назад

very helpful video. Could you please tell how we can compare two columns (A & B) with other two columns (C & D). for instance, if mobile price is also there, then four columns will come. Them I want to compare two columns (A & B) with other two columns (C &D) .

@ExcelMacroMania Год назад

You can add more variables (col1, col2, col3, col4), and define prod1, price1, prod2, price2, then add below a condition to check if price1 = price2 when matching the phone model. Based on the code in the blog post, tht would be something like this: ... For r = 2 To lr prod1 = Cells(r, col1.Column).Value price1= Cells(r, col2.Column).Value prod2 = Cells(r, col3.Column).Value price2= Cells(r, col4.Column).Value 'check if prod1 and price1 is the same in col2 If prod1 "" And price1"" Then Set incol2 = col2.Find(prod1) If incol2 Is Nothing And price1=price2 Then Cells(r, col1.Column).Interior.Color = vbYellow Cells(r, col2.Column).Interior.Color = vbYellow End If End If 'and the same for the other block of code (check if prod2 is in col1 Next r

@sumiths9284 Год назад

@@ExcelMacroMania Thank you so much.

@SathurshiniThuraisamy Год назад

Could you tell us how to insert a row if col2 doesnt match with col1?

@ExcelMacroMania 9 месяцев назад

Columns("B").Insert Inserts a new empty column in column B and moves whatever was there to the right by default

@mldailyvirals5798 2 года назад

What if you want to highlight the matched value and not the diffrence value?

@ExcelMacroMania 2 года назад

Just reverse the conditional statement for both columns as: If Not isincol2 Is Nothing Then ... If Not isincol1 Is Nothing Then ...

@sagnikghosh341 Год назад

getting a error 424 - object not found while running this macro? Need help!

@ExcelMacroMania Год назад

Error 424 is usually due to a miss-spelled function. Which line causes the error? Do some debugging or use error handlers, learn more about that in the session 10 of the tutorial for beginners in this channel. You can also see the correct macro code in this post: excelmacroclass.blogspot.com/2022/04/compare-two-columns-with-data.html

@TanvirMostofa Год назад

I want to know how to solution the three criteria index match function in Visual basic(VBA) excel file

@ExcelMacroMania Год назад

This is not really connected with the topic of the video but here's how you use Index and Match with VBA: WorksheetFunction.Index(Range("A1:A10"), WorksheetFunction.Match("target value", Range("B1:B10"), 0))

@neilryanlim8228 Год назад

Please advise if the data are numbers because when i tried the code its not working the expected result

@ExcelMacroMania Год назад

The expected result is highlighting in yellow the different values, in your case, different numbers between the 2 columns. If those are not highlighted can be due to different formatting (General vs Number, for example), or decimals, or numbers as a result of a formula... Another thing you can try is to find a match against the whole of the search (instead of part) adding LookAt:=xlWhole to the Find method in the parenthesis and separated with a comma.

@lsd1999nardo Год назад

Could you please assist me? I have both information in a specific sheet and have referenced it. But code is still running on active sheet instead, is there some other place i needto change the code? Set col1 = Sheets("CorrectSheet").Columns("A") Set col2 = Sheets("CorrectSheet").Columns("C") lr = Sheets("CorrectSheet").Columns("A:C").SpecialCells(xlCellTypeLastCell).Row

@ExcelMacroMania Год назад

You need to change it also for prod1 and prod2: prod1 = Sheets("CorrectSheet").Cells(r, col1.Column).Value prod2 = Sheets("CorrectSheet").Cells(r, col2.Column).Value And also to highlight the cell in the correct sheet: Sheets("CorrectSheet").Cells(r, col1.Column).Interior.Color = vbYellow

@sumiths9284 Год назад

Could you please tell how to align two columns properly. Suppose If Column A has 10 products and and Column B has also has the same 10 products but in different order. I want to align the products in such a way that same products should share the same row.

@ExcelMacroMania Год назад

I believe you want to SORT the values and that's explained in detail around the minute 8 of this other video: sites.google.com/view/sudokupro The video is about the Range object, as that's a method of the Range object. For your 2 columns (A and B), that would basically be something like this: Dim col1 As Range, col2 As Range Set col1 = Columns("A") Set col2 = Columns("B") col1.Sort Range("A1"), xlAscending col2.Sort Range("B1"), xlAscending

@rimantasdanilevicius6754 Год назад

Hello, this content is very useful. I have question - how to update code in order to get report that (e.g time 7:25 for context) vba could create some report on separate sheet with info about which rows have been changed (in this case - write down rows which products is not in column A. So lets say row 6 | Nokia 3310 Classic, row 7 Samsung Galaxy A10 and so on. Why variables r and lr are not defined , but your code works and mine does not? What kind variables are dif and iscol1? VBA does not working on my PC...

@ExcelMacroMania Год назад

Hello, all variables should indeed be declared, but the macro still works if not declared (VBA takes the variable as a Variant). The variable dif is an Integer, is just a counter of differences, while variable iscol1 (or isincol1 or incol1 in the blog post) is a Variant because the method Find can generate a string or number or null depending what's in the cell. Have a look at the blog post to see the correct code: excelmacroclass.blogspot.com/2022/04/compare-two-columns-with-data.html

@rimantasdanilevicius6754 Год назад

@@ExcelMacroMania Hello, thank you for link to the code. It works. I have last question. How can I add additional validation that code should format cell if that cell is empty? I can find place how to add if prod2 == " " then format cell yellow too. For r = 1 To lr prod1 = Cells(r, col1.Column).Value prod2 = Cells(r, col2.Column).Value 'check if prod2 is in col1 If prod2 prod1 Then Set incol1 = col1.Find(prod2) If incol1 Is Nothing Then Cells(r, col2.Column).Interior.Color = vbYellow dif2 = dif2 + 1 Sheets("difs").Cells(dif2, 2).Value = prod2 'needs sheet difs added End If End If Next r

@ExcelMacroMania Год назад

@@rimantasdanilevicius6754 You can add an If statement before checking if prod2 is in col1... something like: If prod1"" Then .... Else Cells(r, col1.Column).Interior.Color = vbYellow End if or you can put it together with the existing condition: If prod1="" Or incol1 Is Nothing Then Cells(r, col2.Column).Interior.Color = vbYellow

@ytjegr 4 месяца назад

Hi..i used your code to compare two columns. But if the row counts are more( in my case 30000+ rows), its take more time to complete the procress. Is there any way to improve the speed with this same code. Could you please help me what are all the way to improve this code.

@ExcelMacroMania 4 месяца назад

Try adding Application.ScreenUpdating = False, and maybe also Application.EnableEvents = False, and Application.Calculation = xlCalculationManual, at the beginning of the macro. Depending on the macro, using With statement will be faster, and using arrays instead of comparing the values in the Range is also faster.

@ytjegr 4 месяца назад

@@ExcelMacroMania Thank you for your reply...i will add and check

@omprasaddehury2109 2 года назад

Hello Thanks for this video. However here you are highlighting the differences in the same sheet. If I have Mobile phones sold 2020 and Mobile Phones sold 2022 in diffs sheet and I want to highlight the duplicates in both the sheets. Please note that there are multiple duplicate rows in the same column. Kindly suggest as I am very new to VBA.

@ExcelMacroMania 2 года назад

Yes you are right, that refers to the active worksheet. I uploaded the code to my blog today: excelmacroclass.blogspot.com/2022/04/compare-two-columns-with-data.html But if the two columns are in different sheets (or the sheet is not active when running the macro), you need to specify which sheet it refers to. For example, when setting col1 and col2, you just add: Set col1 = Sheets("Sheet1").Columns("A") Set col2 = Sheets("Sheet2").Columns("C") You could further reference a workbook object if the two lists are in separate workbooks. Then you need to add that to a couple of other places in the code: for prod1 and prod2, and later when highlighting the cells too. The last row with content (lr) won't be accurate now, you may have to get lr1 and lr2 (if the lists have different length), or just have a value such as lr_max. Hope that helps.

@omprasaddehury2109 2 года назад

@@ExcelMacroMania Thanks for the update. I will try and hope this will fix my issue.

@mldailyvirals5798 2 года назад

How to look for a a dtaa on 1 colomn with the exact value. Ex Low,low Low, loW Low,Low only must be highlighted the exact value

@ExcelMacroMania 2 года назад

You need to set MatchCase to True in Find method (change it in both places as shown below): Set isincol2 = col2.Find(prod1, MatchCase:=True) Set isincol1 = col1.Find(prod2, MatchCase:=True)

@irempeker6239 2 года назад

@@ExcelMacroMania Hello thanks for the update, i didnt get which fields to update if the lists are in different workbooks. Could you be so kind to help?

@TanvirMostofa Год назад

Dear sir, I want to know how to solution the three criteria index match function in Visual basic excel file ? Can i shear a excel file this related problem.

@ExcelMacroMania Год назад

Find info about INDEX and MATCH formulas here: support.microsoft.com/en-us/office/look-up-values-with-vlookup-index-or-match-68297403-7c3c-4150-9e3c-4d348188976b You can use those functions in your macros using the WorksheetFunction object. Learn more about that object here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-VbEm38bc8n4.html WorksheetFunction.Index... WorksheetFunction.Match...

@ytjegr 6 месяцев назад

Hi..could you please help to write a code . I want to compare two columns with user input. Example: user gave input word "motorola", then it should start select the first "motorala" word in row of column A and it will select the rows till next "motorola" word in the column A (in this expample row 4 to 11). Same senario in Column B . then compare selected rows alone from Column A and B.

@ExcelMacroMania 6 месяцев назад

That requires a big modification of the code. You need to use the second argument of "Find" to set the start position to do the search within the range. For example, let's say you find the keyword (motorola) in row 4, the variable startPos is set to the next row index so that in the next loop starts looking from row 5. You save those values to other variable, let's say r1 and r2, to select all rows between them (for example with Rows(r1 & ":" & r2).Select). Good luck! startPos = 0 Set incol2 = col2.Find(prod1, startPos) startPos = incol2.Row + 1

@TanvirMostofa Год назад

Can i shear a excel file ? Please send your mail

@ExcelMacroMania Год назад

Please contact me via the contact form of any of my blogs and I will reach out to your email: excelmacroclass.blogspot.com/p/contact_8.html

@TanvirMostofa Год назад

Dear sir, I am facing some problems with the index match function (Vba macro Excel) But i can not explain.I want to share an excel file to describe . I need help. Please help me . If you want to help me, please send your mail address to share the file.

@ExcelMacroMania Год назад

Please contact me via the contact form of any of my blogs and I will reach out to your email: excelmacroclass.blogspot.com/p/contact_8.html

@TanvirMostofa Год назад

Can i shear a excel file ? Please send your mail

@ExcelMacroMania Год назад

You can reach me at xlsgsdata@gmail.com