Find differences between rows and create list in new sheet (Excel VBA)

  • For the purposes of the problem I'm trying to solve, I've attached a file with sample data showing exactly what I want to do: SampleData3.xlsx

    Please let me know if I can clarify anything I outline - it's a bit difficult to explain.


    In Sheet1 of this workbook, I have 4 columns of particular importance: UI1, UI2, UI3, and UI4 (columns C, E, I, and J, respectively). UI stands for "unique identifier". Column UI1 coincides with UI3, and UI2 coincides with UI4. I am only concerned with rows for which either UI1 or UI2 are different from UI3 and UI4. In other words, I want to consider rows where the values in columns C or E are different from the values in columns I or J.

    For example, in row 3 of this sample data, columns UI1 and UI3 are the same, AND UI2 and UI4 are the same, therefore there is an exact match in this row and I do not care about it.


    However, rows 4 and 5 fit my criteria: in row 4, only UI2 and UI4 are different, and in row 5, both corresponding pairs (UI1/UI3 and UI2/UI4) are different. In either case, I want to write a macro that finds these differences and creates a list exactly like you see in Sheet2 of the workbook.

    So, when the macro finishes running on this sample data in Sheet1, we should see 2 rows in this new sheet (Sheet2).


    Column A will contain the concatenation of UI3 and UI4 (i.e., for row 4, UI3 = 0071-135, and UI4 = 1, so cell A2 in Sheet2 should read "0071-135-1"). Another example: "0071-130" and "1" should concatenate and become "0070-130-1". Notice that a hyphen is to be added between the concatenated strings, so it's not a "strict" concatenation.

    Column D in Sheet2 will be the value from Column K of Sheet1, Column E in Sheet2 will be the value from Column F of Sheet1, and Column F of Sheet2 will be the value of Column G from Sheet1. Everything highlighted in yellow in my sample data is information that should be populated with the macro.

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

  • Hi Mumps,

    This is awesome, thank you so much! Hope you will not mind if I take this one step further :)

    Let's say I've added a new sheet (Sheet3), and I've also added a new column highlighted in red (H) in Sheet2 with a long list of unique IDs. These IDs relate to the ones recently generated in column A. This new column H will be much longer than the list you helped me generate with the sub you wrote above since it will contain the list of all available IDs and not just the sample ones I provided.


    I want to filter even more on the new list just created. I want to check column A against column H for matches. If there is a match, do nothing. If there is NO match (i.e., no ID in column A matches any ID in column H), then I want those "no match" rows (highlighted in blue) to create a list in Sheet3.

    I've attached a new sample sheet here: SampleData3.xlsx

    I've also tried to edit your code to fit this second problem to be solved. The problem is that, in this case, I want to check A2 against all values from column H (not just H2), A3 against all values from column H (not just H3), and so on. How can I edit this code to fit the scenario?

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