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.
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...
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
@@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
@@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
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.
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
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!!!
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.
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
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 🙂
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) .
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
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.
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
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
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
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.
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.
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.
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: th-cam.com/video/VbEm38bc8n4/w-d-xo.html WorksheetFunction.Index... WorksheetFunction.Match...
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))
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.
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
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.
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.
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)
@@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?
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
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.
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.
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.
Thanks for the feedback, truly appreciated!
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.
Thanks for your words, glad to hear that!
Good video. Straight to the point.
Thank for your time to guide.
Wow thank you very much
Thank you so much
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...
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
@@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
@@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
Thank you. This is excellent. It would nice if it can compare numeric values.
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.
very good video, can you make a video, if value found in column C, then copy the row and paste to another sheet.
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
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!!!
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.
Very nice tutorial. Indeed, what if I want to extract in the same worksheet instead of others? For example in Column D?
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.
Very helpful video.
How do you extract the discrepancies to other column?
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
Hi, You can do the same thing without the use of a macro, with "Conditional Formatting". At least in Excel 2019.
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 🙂
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) .
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
@@ExcelMacroMania Thank you so much.
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.
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
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
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
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.
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.
@@ExcelMacroMania Thank you for your reply...i will add and check
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.
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: th-cam.com/video/VbEm38bc8n4/w-d-xo.html
WorksheetFunction.Index...
WorksheetFunction.Match...
I want to know how to solution the three criteria index match function in Visual basic(VBA) excel file
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))
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.
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
Could you tell us how to insert a row if col2 doesnt match with col1?
Columns("B").Insert
Inserts a new empty column in column B and moves whatever was there to the right by default
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.
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.
@@ExcelMacroMania Thanks for the update. I will try and hope this will fix my issue.
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
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)
@@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?
getting a error 424 - object not found while running this macro? Need help!
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
Please advise if the data are numbers because when i tried the code its not working the expected result
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.
What if you want to highlight the matched value and not the diffrence value?
Just reverse the conditional statement for both columns as:
If Not isincol2 Is Nothing Then ...
If Not isincol1 Is Nothing Then ...
Can i shear a excel file ? Please send your mail
You can reach me at xlsgsdata@gmail.com
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.
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
Can i shear a excel file ? Please send your mail
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