Help Matching Two Columns and Showing Non-Matching Values

[Pharoh348]

Hello,

I am working on a project and it has been a while since I've had to use any VBA, so I'm saving the time and asking. I have data on two separate worksheets. I want to compare column B on Sheet 1 with column N on Sheet 2. Any values that do not match between the two columns I would like to have generate in a listbox on a userform. Any input is greatly appreciated. Thank!

 

[DanteAmor]

Hi @Pharoh348, welcome to the forum!

Put following code in your userform.
Change "Sheet1" and "Sheet2" for names of your sheets.
Create an userform with commmandbutton and listbox, change listbox columncount property to 2 columns.

Open userform and press button.

Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a() As Variant, b As Variant, c() As Variant
  Dim i As Long, n As Variant
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  a = sh1.Range("B1", sh1.Range("B" & Rows.Count).End(xlUp))
  b = sh2.Range("N1", sh2.Range("N" & Rows.Count).End(xlUp))
  '
  'Find values from sheet1 on sheet2
  For i = 1 To UBound(a)
    n = Application.Match(a(i, 1), Application.Index(b, 0, 1), 0)
    If IsError(n) Then
      ListBox1.AddItem sh1.Name
      ListBox1.List(ListBox1.ListCount - 1, 1) = a(i, 1)
    End If
  Next
  '
  'Find values from sheet2 on sheet1
  For i = 1 To UBound(b)
    n = Application.Match(b(i, 1), Application.Index(a, 0, 1), 0)
    If IsError(n) Then
      ListBox1.AddItem sh2.Name
      ListBox1.List(ListBox1.ListCount - 1, 1) = b(i, 1)
    End If
  Next
End Sub

 

[Pharoh348]

Thank you so much!

 

[DanteAmor]

Youre welcome & thanks for the feedback.