Excel 2010 - Take whole rows from one sheet and display them in the 2nd sheet based on match in 3rd sheet

Question

I'll cut to the chase:

Excel 2010

I have Sheet1 with a list of orders. Customer name is in column F.

Sheet2 contains list of customers with just their names in column A.

I would like to run a macro / vlookup / index (whatever works) to run through the list of thousands of orders in Sheet1 and copy to Sheet3 the rows (whole rows) where the customer name from Sheet2 matches.

Sheet1 has 10 columns and about 10000 rows. Sheet2 has about 50 rows in column A only.

I have tried a few types of macros and did a proper search but couldn't find anything that would work. I would paste what I have but that would only be an embarrassment.

I might add that I will need to run this weekly and the list of orders will be changing. List of names may too but with little frequency.

Can you help? :)

EDIT: From the answers so far I can see that I would need to use VBA script. I would appreciate help with that as I know next to nothing about the language. I should be able to manage modifying the script to suit my needs but I need something to start. Perhaps this pseudocode will make it easier for you good people:

Read data from A1:A50 in Sheet2

Compare F1, Sheet1 with A1:A50, Sheet2

If no match - continue to F2

If match - copy whole row to A1 in Sheet3

(...)

Compare Fx, Sheet1 with A1:A50, Sheet2

If no match - continue to Fx+1

If match - copy whole row to A(n+1) [n=number of matches] in Sheet3

Continue comparing rows in Sheet1 to A1:A50, Sheet2 and return next match in An, Sheet3

So far I have something like this but the problem is that it returns data to Sheet3 only if row number in Sheet2 matches row number in Sheet1. Also, it returns it to Sheet3, row number matching Sheet1. So I end up with one result in row 45 in Sheet3 instead of around 20 results in rows 1-20.

Sub asd()
For Counter = 1 To Sheets(1).UsedRange.Rows.Count
    If Sheets(1).Range("F" & Counter).Value = Sheets(2).Range("A" & Counter).Value Then
      Sheets(3).Range("C" & (Counter)).Value = Sheets(1).Range("A" & Counter).Value
      Sheets(3).Range("D" & (Counter)).Value = Sheets(1).Range("B" & Counter).Value
    End If
Next Counter
End Sub

Thank you, Pat

Create a copy of Sheet1 and name it "Orders". In a new column (Column 11) on the "Orders" sheet , use the match formula to see when column F matches column A in sheet2. Then filter the "Orders" sheet and delete all of the #NA's in your data and you are done. — rwilson
Thanks rwilson for your reply. I have a feeling though that it won't be as straightforward as I hope it to be for repeated, weekly use. Especially given that I want to share the method / macro for use by my colleagues. Thanks again. — Pats
I'm just giving you the steps you can take in Excel to complete the process. Using these steps, the macro recorder, and a little work, you should be able to do this. Nobody on SO knows your data better than you do. — rwilson

Tnimni Tnimni · Accepted Answer · 2015-08-19T15:53:16

I do believe you'll need to use VBA script to copy the entire row.

Please make sure your data in both sheets 1 & 2 is sorted if you wish to use vlookup, but I don't think it will help since vlookup will "stop" on the first hit, meaning if you have a customer names John on sheet1 which repeats several times, vlookup will only pick the first occurrence

Regards,

Excel 2010 - Take whole rows from one sheet and display them in the 2nd sheet based on match in 3rd sheet

1 Answers