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

pijo · Dec 12th 2019

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.

Mumps · Dec 12th 2019

Try:

Code

Sub CreateList()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, i As Long, v1 As Variant, LastRow As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    v1 = srcWS.Range("C3", srcWS.Range("C" & Rows.Count).End(xlUp)).Resize(, 9).Value
    For i = 1 To UBound(v1, 1)
        If Not (v1(i, 1) = v1(i, 7) And v1(i, 3) = v1(i, 8)) Then
            LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            With desWS
                .Range("A" & LastRow) = v1(i, 7) & "-" & v1(i, 8)
                .Range("D" & LastRow) = v1(i, 9)
                .Range("E" & LastRow) = v1(i, 4)
                .Range("F" & LastRow) = v1(i, 5)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Display More

pijo · Dec 12th 2019

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?

Code

Sub CreateFinalList()

Application.ScreenUpdating = False

Dim srcWS As Worksheet, desWS As Worksheet, i As Long, v1 As Variant, LastRow As Long

Set srcWS = Sheets("Sheet2")
Set desWS = Sheets("Sheet3")
v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Value

For i = 1 To UBound(v1, 1)
If v1(i, 1) = v1(i, 8) Then
LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
With desWS
.Range("A" & LastRow) = v1(i, 1)
.Range("D" & LastRow) = v1(i, 4)
.Range("E" & LastRow) = v1(i, 5)
.Range("F" & LastRow) = v1(i, 6)
End With
End If
Next i

Application.ScreenUpdating = True

End Sub

Display More

Mumps · Dec 13th 2019

Try:

Code

Sub CreateFinalList()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, i As Long, v1 As Variant, v2 As Variant, LastRow As Long
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet3")
    v1 = srcWS.Range("H2", srcWS.Range("H" & Rows.Count).End(xlUp)).Value
    v2 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 7).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v1, 1)
            If Not .Exists(v1(i, 1)) Then
                .Add v1(i, 1), Nothing
            End If
        Next i
        For i = 1 To UBound(v2, 1)
            If Not .Exists(v2(i, 1)) Then
                LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                With desWS
                    .Range("A" & LastRow).Resize(, 7) = Array(v2(i, 1), v2(i, 2), v2(i, 3), v2(i, 4), v2(i, 5), v2(i, 6), v2(i, 7))
                End With
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Display More

pijo · Dec 13th 2019

You are a wizard - this does exactly what I wanted. Thank you!!!

Mumps · Dec 13th 2019

You are very welcome.

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

Participate now!

Share

Tags