Compare Two Columns, Return Missing Values in Separate Columns

[censo]

Greetings,

I have 2 columns that are supposed to have equal values. I need assistance creating a macro that will compare both columns A:B and return values that are missing in columns C:D

If both columns match, do nothing (return no values)

Below is a visual example of the desired outcome

new_thread_post.xlsx
	A	B	C	D
1	SKU SOURCE 1	SKU SOURCE 2	MISSING FROM COLUMN A	MISSING FROM COLUMN B
2	ABC-123	ABC-123	ABC-123-003	ABC-123-001
3	ABC-123-001	ABC-123-002	ABC-123-007	ABC-123-005
4	ABC-123-002	ABC-123-003		ABC-123-010
5	ABC-123-004	ABC-123-004
6	ABC-123-005	ABC-123-006
7	ABC-123-006	ABC-123-007
8	ABC-123-008	ABC-123-008
9	ABC-123-009	ABC-123-009
10	ABC-123-010
Sheet1

 

[Joe4]

Here is one way:

Sub PerformMatch()

    Dim lrA As Long
    Dim lrB As Long
    Dim rngA As Range
    Dim rngB As Range
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
'   Find last row with data in column B
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Set data ranges
    Set rngA = Range("A2:A" & lrA)
    Set rngB = Range("B2:B" & lrB)
    
'   Loop through all rows in column B
    For Each cell In rngB
'       Search for value in column A
        If Application.WorksheetFunction.CountIf(rngA, cell.Value) = 0 Then
'           Copy value to column D
            Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell

'   Loop through all rows in column A
    For Each cell In rngA
'       Search for value in column B
        If Application.WorksheetFunction.CountIf(rngB, cell.Value) = 0 Then
'           Copy value to column D
            Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell
     
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[censo]

Worked beautifully, and complete with notation so I can understand what's going on under the hood.

Thank you SO much!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[ibmy]

Here is one way:

Sub PerformMatch()

    Dim lrA As Long
    Dim lrB As Long
    Dim rngA As Range
    Dim rngB As Range
    Dim cell As Range
  
    Application.ScreenUpdating = False
  
'   Find last row with data in column A
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
'   Find last row with data in column B
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
  
'   Set data ranges
    Set rngA = Range("A2:A" & lrA)
    Set rngB = Range("B2:B" & lrB)
  
'   Loop through all rows in column B
    For Each cell In rngB
'       Search for value in column A
        If Application.WorksheetFunction.CountIf(rngA, cell.Value) = 0 Then
'           Copy value to column D
            Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell

'   Loop through all rows in column A
    For Each cell In rngA
'       Search for value in column B
        If Application.WorksheetFunction.CountIf(rngB, cell.Value) = 0 Then
'           Copy value to column D
            Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell
   
    Application.ScreenUpdating = True
  
    MsgBox "Macro complete!"
  
End Sub

Thanks for the code @Joe4

Is there any way to make the code to copy missing value from column A to below last row of column A instead of to new column C,
same as for column B, copy missing value from column B to below last row of column B instead of to new column D.

 

[Joe4]

Thanks for the code @Joe4

Is there any way to make the code to copy missing value from column A to below last row of column A instead of to new column C,
same as for column B, copy missing value from column B to below last row of column B instead of to new column D.

I would leave the code as-is, and then just add code that move the values from columns C and D to the bottom of A and B afterwards, so as not to interfere with the comparisons for the initial run, i.e.

Sub PerformMatch()

    Dim lrA As Long
    Dim lrB As Long
    Dim rngA As Range
    Dim rngB As Range
    Dim cell As Range
    Dim lrC As Long
    Dim lrD As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
'   Find last row with data in column B
    lrB = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Set data ranges
    Set rngA = Range("A2:A" & lrA)
    Set rngB = Range("B2:B" & lrB)
    
'   Loop through all rows in column B
    For Each cell In rngB
'       Search for value in column A
        If Application.WorksheetFunction.CountIf(rngA, cell.Value) = 0 Then
'           Copy value to column D
            Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell

'   Loop through all rows in column A
    For Each cell In rngA
'       Search for value in column B
        If Application.WorksheetFunction.CountIf(rngB, cell.Value) = 0 Then
'           Copy value to column D
            Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = cell.Value
        End If
    Next cell
    
'   Find last rows in columns C and D with data
    lrC = Cells(Rows.Count, "C").End(xlUp).Row
    lrD = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Cut/paste values to bottom of columns A and B
    If lrC > 1 Then Range("C2:C" & lrC).Cut Range("A" & lrA + 1)
    If lrD > 1 Then Range("D2:D" & lrD).Cut Range("B" & lrB + 1)
     
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub