Help Matching Two Columns and Showing Non-Matching Values

Pharoh348

New Member
Joined
Nov 14, 2019
Messages
2
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!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,204
Office Version
2007
Platform
Windows
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.

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,204
Office Version
2007
Platform
Windows
Youre welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,552
Messages
5,487,523
Members
407,604
Latest member
sama9000

This Week's Hot Topics

Top