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!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,759
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
7,759
Office Version
2007
Platform
Windows
Youre welcome & thanks for the feedback.
 

Forum statistics

Threads
1,077,855
Messages
5,336,782
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top