I have two different excel file, ex WB1, WB2. Need to compare column from WB1, ex: column H to another workbook WB2 with specific column Ex: column F and highlight the difference in column H of WB1. column contains both text and numbers. I know it possible using vlookup, but need to automate this using macro, can I someone provide macro to do the same? Thanks in advance
Macro to compare of two columns from two different workbook
 Moorthy24
 Thread is marked as Resolved.



Could you attach a copy of your two files? It would be easier to see how you data is organized and to test possible solutions. Include a detailed explanation of what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets. Desensitize the data if necessary.

Attached two sample files, WB1 & WB2. WB1 is main file. text in column H should be compared with column F of WB2. If any match, it needs to be highlighted in column H of WB1.

Make sure that both workbooks are open and place this macro in WB1. Change the workbook names and sheet names to suit your needs.
Code
Display MoreSub CompareCols() Application.ScreenUpdating = False Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet Set WS1 = ThisWorkbook.Sheets("WB1") Set WS2 = Workbooks("sample_WB2.xlsx").Sheets("vulnerabilities.jsoncriticalo") Set RngList = CreateObject("Scripting.Dictionary") For Each Rng In WS2.Range("F2", WS2.Range("F" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = 6 End If Next Application.ScreenUpdating = True End Sub

It worked, may I ask further help? Similar kind of comparison should be multiple sheets. WB1 is the primary sheet should be compared with WB2, WB3 & WB4. With single macro compare WB2, WB3 & WB4 one after another and highlight the matches in different color. WB2 = Yellow, WB3 = Blue, WB4 = Green. I have attached sample WB3 file, do not have WB4 file, please assume Column B in WB4 to be verified.
Thanks for the fantastic work, it is much appreciated.

Just a note that if the value on WB1exists on more than one of the other sheets, the value on WB1 will be colored according to the color of the last sheet on which it existed. For example, if ABC2222123345 existed in WB2, it would be colored yellow. However, if it also exists in WB4, the yellow will be overwritten with green. If you are looking to rely on the color to indicate in which sheet a value was found, you will always get the color of the last sheet in which it was found. I'm not sure if that is what you want. Does that makes sense?

Yes, I do understand that and though about that as well before defining the requirement. The script can follow the comparison in the order of WB2, WB3 and WB4 with WB1. If the same value repeat on all the sheets and it is reflecting the color the WB4 is fine. Thanks

Try this macro. To make it easier to program, the colors it produces aren't exactly what you requested. Hopefully that's OK with you. Change the workbook and sheet names to suit your needs.
Code
Display MoreSub CompareCols() Application.ScreenUpdating = False Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, x As Long: x = 6 Set WS1 = ThisWorkbook.Sheets("WB1") Set WS2 = Workbooks("sample_WB2.xlsx").Sheets("vulnerabilities.jsoncriticalo") Set WS3 = Workbooks("sample_WB3.xlsx").Sheets("Sheet1") Set WS4 = Workbooks("sample_WB4.xlsx").Sheets("Sheet1") Set RngList = CreateObject("Scripting.Dictionary") For Each Rng In WS2.Range("F2", WS2.Range("F" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next RngList.RemoveAll x = x + 1 For Each Rng In WS3.Range("Q2", WS3.Range("Q" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next RngList.RemoveAll x = x + 1 For Each Rng In WS4.Range("B2", WS4.Range("B" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next Application.ScreenUpdating = True End Sub

This is working perfectly. May I ask you another help? Can I get column inserted after column H in WB1, may be column I, and update the text 'matching' for the matching values in column H.

Insert this code:
Code
Display MoreFor Each Rng In WS1.Range("I2", WS1.Range("I" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next Rng RngList.RemoveAll x = x + 1
immediately below this line of code;

I have inset the code as suggested, below the line of Set RngList = CreateObject("Scripting.Dictionary"), it is not working. Here is the complete code, please correct if anything wrong.
Sub CompareColsaddcol()
Application.ScreenUpdating = False
Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, x As Long: x = 6
Set WS1 = ThisWorkbook.Sheets("WB1")
Set WS2 = Workbooks("sample_WB2.xlsx").Sheets("vulnerabilities.jsoncriticalo")
Set WS3 = Workbooks("sample_WB3.xlsx").Sheets("Sheet1")
Set WS4 = Workbooks("sample_WB4.xlsx").Sheets("Sheet1")
Set RngList = CreateObject("Scripting.Dictionary")
For Each Rng In WS1.Range("I2", WS1.Range("I" & Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next Rng
For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))
If RngList.Exists(Rng.Value) Then
WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x
End If
Next Rng
RngList.RemoveAll
x = x + 1
For Each Rng In WS2.Range("F2", WS2.Range("F" & Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next Rng
For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))
If RngList.Exists(Rng.Value) Then
WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x
End If
Next
RngList.RemoveAll
x = x + 1
For Each Rng In WS3.Range("Q2", WS3.Range("Q" & Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next Rng
For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))
If RngList.Exists(Rng.Value) Then
WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x
End If
Next
RngList.RemoveAll
x = x + 1
For Each Rng In WS4.Range("B2", WS4.Range("B" & Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next Rng
For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp))
If RngList.Exists(Rng.Value) Then
WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x
End If
Next
Application.ScreenUpdating = True
End Sub

Just for future reference, first thing to correct is your code should go in code tags. That's the "</>" symbol in the upper right corner.
Code
Display MoreSub CompareColsaddcol() Application.ScreenUpdating = False Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, x As Long: x = 6 Set WS1 = ThisWorkbook.Sheets("WB1") Set WS2 = Workbooks("sample_WB2.xlsx").Sheets("vulnerabilities.jsoncriticalo") Set WS3 = Workbooks("sample_WB3.xlsx").Sheets("Sheet1") Set WS4 = Workbooks("sample_WB4.xlsx").Sheets("Sheet1") Set RngList = CreateObject("Scripting.Dictionary") For Each Rng In WS1.Range("I2", WS1.Range("I" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next Rng RngList.RemoveAll x = x + 1 For Each Rng In WS2.Range("F2", WS2.Range("F" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next RngList.RemoveAll x = x + 1 For Each Rng In WS3.Range("Q2", WS3.Range("Q" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next RngList.RemoveAll x = x + 1 For Each Rng In WS4.Range("B2", WS4.Range("B" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next Application.ScreenUpdating = True End Sub

Your second problem if i'm reading it right, If you get a match in column "H" you want the VBA to insert a column "I" and say "Matching" in the corresponding cell. If i am in fact assuming your needs correctly, than before any time is spent building this code, there is an issue straight off. A "Column" can only be inserted once so for example: if you have a match in cell A1, a column would be inserted that would then become column "I" and the word "Matching" would be inserted into "I1". If you had another match in A2, another column would be inserted and IT would then be Column "I". This would move your previous match one column to the right moving the word "Matching" into "J1". and so on. i hope this makes sense. It would be my opinion to just manually add the column and just set the code to give you the cell value of "Matching" rather than inserting a column.

Please attach a revised copy of your file that includes the new column with the values.
I don't think that the OP wants to macro to insert the new column. I think that the column with the data to compare is already in the workbook.

mycomputerguyw , thanks for notifying, i will use </> to insert the column from next time. I understood that I must insert column I manually to update the word 'matching' using script. I have tried that, I scan see only the matching cells are updated in column 'H', but column 'I' not updated with word 'matching'. I'm attaching the copy of the file.
@Mump yes, I'm looking for way to update column I with the word matching.
Thanks.

Are you saying that instead of highlighting the matched values, you want to put the matched values into column I? If that is the case, then you have to be aware that the value that will go into column I will always be the value matched in the last workbook, similar to what happened with the color highlighting. If you want to show the matching values from all the workbooks, then you could do that by adding a new column in WB1 for each workbook. Please clarify in detail.

Means that Ex: if 'H5' in WB1 match with WB2 and doesn't match with WB3, the last value will be blank, I5 will be empty. May I ask you to add the text 'matching' only for the cells that matches with file sample_WB4.xlsx? Thanks

I'm sorry but I don't understand.
if 'H5' in WB1 match with WB2 and doesn't match with WB3, the last value will be blank, I5 will be empty
What happens if H5 in WB1 doesn't match with WB2 but matches with WB3?

Sorry, if I'm not communicating rightly, can continue with the coloring as it is for all the matching workbooks, WB2, WB3, and WB4. The required change is to add the word 'matching' in column 'I' if a cell in column 'H' matches with sample_WB4 file. Thanks.

Try:
Code
Display MoreSub CompareCols() Application.ScreenUpdating = False Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet, x As Long: x = 6 Set WS1 = ThisWorkbook.Sheets("WB1") Set WS2 = Workbooks("sample_WB2.xlsx").Sheets("vulnerabilities.jsoncriticalo") Set WS3 = Workbooks("sample_WB3.xlsx").Sheets("Sheet1") Set WS4 = Workbooks("sample_WB4.xlsx").Sheets("Sheet1") Set RngList = CreateObject("Scripting.Dictionary") For Each Rng In WS2.Range("F2", WS2.Range("F" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next Rng RngList.RemoveAll x = x + 1 For Each Rng In WS3.Range("Q2", WS3.Range("Q" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x End If Next Rng RngList.RemoveAll x = x + 1 For Each Rng In WS4.Range("B2", WS4.Range("B" & Rows.Count).End(xlUp)) If Not RngList.Exists(Rng.Value) Then RngList.Add Rng.Value, Nothing End If Next Rng For Each Rng In WS1.Range("H2", WS1.Range("H" & Rows.Count).End(xlUp)) If RngList.Exists(Rng.Value) Then WS1.Cells(Rng.Row, 8).Interior.ColorIndex = x WS1.Cells(Rng.Row, 9) = "Matching" End If Next Rng Application.ScreenUpdating = True End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!