Find duplicate values using vlookup?

[biggs1320]

I have two sheets, one with an "Interest List" and a second with a "Master List" and associated data. Not all the individuals on the "Interest List" are on the "Master List" and vice versa. Additionally, many on the "Interest List" are listed multiple times on the "Master List"

I need to pull the names and data from the "Master List" for all those on the "Interest List" and put it into a 3rd sheet. Clear as mud? The data set is rather large (~8,000) rows on the "Master List, so I've included some mock data below.

I'm using Office 20013 on a Windows 8.1. Ideally, I would like to accomplish this using formulas, but realize that may not be possible.

Excel 2012
	A
1	Interest List
2	Joe
3	Sue
4	John
5	Laura
Sheet1

Excel 2012
	A	B	C	D	E
1	Master List	Data1	Data2	Data3	Data4
2	John	alpha1-1	numeral1-1	alpha2-1	numeral2-1
3	Sue	alpha1-2	numeral1-2	alpha2-2	numeral2-2
4	Jack	alpha1-3	numeral1-3	alpha2-3	numeral2-3
5	laura	alpha1-4	numeral1-4	alpha2-4	numeral2-4
6	Sue	alpha1-5	numeral1-5	alpha2-5	numeral2-5
7	John	alpha1-6	numeral1-6	alpha2-6	numeral2-6
8	sue	alpha1-7	numeral1-7	alpha2-7	numeral2-7
Sheet2

 

[bruderbell]

How do you feel about Access and SQL queries? I'm not saying it isn't possible in excel, but this would be a very easy query in SQL. Something like:
Select * from [Master List] where [Master List].Name = [Interest List].Name;

 

[hiker95]

biggs1320,

I need to pull the names and data from the "Master List" for all those on the "Interest List" and put it into a 3rd sheet.

Can we have a screenshot of what the results will look like on the 3rd sheet?

 

[shawnhet]

Quick and dirty:

Add a helper column after all your data on the Master List with the following formula.

=iserror(vlookup(A2,Sheet1!A:A,1,False)) -- put in B2 and copy downwards.

Then filter the helper column for all the False ones and copy the filtered results to Sheet3

If you need a case sensitive results (ie not an entry for joe if Joe is on the Interest list)

=EXACT(A2,VLOOKUP(A2,Sheet1!A:A,1,FALSE)) and filter for all the True results.

Cheers,

 

[biggs1320]

biggs1320,



Can we have a screenshot of what the results will look like on the 3rd sheet?

Excel 2012
	A	B	C	D	E
1	Master List	Data1	Data2	Data3	Data4
2	John	alpha1-1	numeral1-1	alpha2-1	numeral2-1
3	Sue	alpha1-2	numeral1-2	alpha2-2	numeral2-2
4	laura	alpha1-4	numeral1-4	alpha2-4	numeral2-4
5	Sue	alpha1-5	numeral1-5	alpha2-5	numeral2-5
6	John	alpha1-6	numeral1-6	alpha2-6	numeral2-6
7	sue	alpha1-7	numeral1-7	alpha2-7	numeral2-7
Sheet3

 

[biggs1320]

Perfect! And using Advanced Filter I can pull the data to a new sheet!

You say quick & dirty, I say simple & elegant solution!

 

[shawnhet]

That's good to hear! Thanks for the feedback.

 

[hiker95]

biggs1320,

Sample worksheet:

Excel 2007
	A
1	Interest List
2	Joe
3	Sue
4	John
5	Laura
6
Sheet1

Excel 2007
	A	B	C	D	E
1	Master List	Data1	Data2	Data3	Data4
2	John	alpha1-1	numeral1-1	alpha2-1	numeral2-1
3	Sue	alpha1-2	numeral1-2	alpha2-2	numeral2-2
4	Jack	alpha1-3	numeral1-3	alpha2-3	numeral2-3
5	laura	alpha1-4	numeral1-4	alpha2-4	numeral2-4
6	Sue	alpha1-5	numeral1-5	alpha2-5	numeral2-5
7	John	alpha1-6	numeral1-6	alpha2-6	numeral2-6
8	sue	alpha1-7	numeral1-7	alpha2-7	numeral2-7
9
Sheet2

Excel 2007
	A	B	C	D	E
1	Master List	Data1	Data2	Data3	Data4
2
3
4
5
6
7
8
Sheet3

After the macro in worksheet Sheet3:

Excel 2007
	A	B	C	D	E
1	Master List	Data1	Data2	Data3	Data4
2	Sue	alpha1-2	numeral1-2	alpha2-2	numeral2-2
3	Sue	alpha1-5	numeral1-5	alpha2-5	numeral2-5
4	sue	alpha1-7	numeral1-7	alpha2-7	numeral2-7
5	John	alpha1-1	numeral1-1	alpha2-1	numeral2-1
6	John	alpha1-6	numeral1-6	alpha2-6	numeral2-6
7	laura	alpha1-4	numeral1-4	alpha2-4	numeral2-4
8
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Compare Text
Sub FindDupes()
' hiker95, 01/29/2014, ME753910
Dim c As Range, d As Range, firstaddress As String
Dim lr As Long, nr As Long
Application.ScreenUpdating = False
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For Each c In Sheets("Sheet1").Range("A2:A" & lr)
  With Sheets("Sheet2").Columns(1)
    firstaddress = ""
    Set d = .Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not d Is Nothing Then
      firstaddress = d.Address
      Do
        nr = Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
        Sheets("Sheet3").Range("A" & nr).Resize(, 5).Value = Sheets("Sheet2").Range("A" & d.Row).Resize(, 5).Value
        Set d = .FindNext(d)
      Loop While Not d Is Nothing And d.Address <> firstaddress
    End If
  End With
Next c
With Sheets("Sheet3")
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindDupes macro.