VBA compare 2 columns and return matches

JWebb

New Member
Joined
Apr 5, 2017
Messages
17
I thank in advance and apologize (for the simplicity) to everyone at the same time. I'm very rusty and need nudged in the right direction.
I have 2 worksheets. 1 is part numbers need for a job and the 2nd is an inventory list. Obviously i'm looking to match part numbers and they are in a "abc123" type format. The "parts needed" list and "On hand" list are varying lengths. I just need to compare the lists and return the matches to a 3rd worksheet. And I need it to be in VBA, the language I cannot remember today. Excel 2016. All sheets are in the same workbook. Lets assume all columns are "A" (I can change accordingly) and we can call the sheets "PN", "OH", and "results".
Not sure if Vlookup or IndexMatch is more appropriate. If more info is needed I will gladly supply it.
Thanks again!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
How about
Code:
Sub JWebb()
   Dim Cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   Dim Ary As Variant, Ky As Variant
   Dim i As Long
   
   Set Ws1 = Sheets("PN")
   Set Ws2 = Sheets("OH")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = False
      Next Cl
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Item(Cl.Value) = True
      Next Cl
      ReDim Ary(1 To .Count, 1 To 2)
      For Each Ky In .Keys
         If .Item(Ky) Then
            i = i + 1
            Ary(i, 1) = Ky
         End If
      Next Ky
   End With
   Sheets("Result").Range("A2").Resize(i, 2).Value = Ary
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top