Macro to compare of two columns from two different workbook

  • 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

  • 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. De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Make sure that both workbooks are open and place this macro in WB1. Change the workbook names and sheet names to suit your needs.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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 ABC-2222-123345 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?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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:

    immediately below this line of code;

    Code
    1. Set RngList = CreateObject("Scripting.Dictionary")

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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.json-critical-o")

    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.


  • 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.

  • Moorthy24

    Please attach a revised copy of your file that includes the new column with the values.


    mycomputerguy-w

    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.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • mycomputerguy-w , 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.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • 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:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.