Excel Macro

[Majus]

I need some help with a macro that could copy rows of data from an existing spreadsheet to a new one based on values in 1 column.

For example, the following table is the original spreadsheet.


ID
123 Data 1 Data 2 Data 3 Data 4 Data 5
156 Data 1 Data 2 Data 3 Data 4 Data 5
124 Data 1 Data 2 Data 3 Data 4 Data 5
167 Data 1 Data 2 Data 3 Data 4 Data 5
187 Data 1 Data 2 Data 3 Data 4 Data 5
143 Data 1 Data 2 Data 3 Data 4 Data 5
121 Data 1 Data 2 Data 3 Data 4 Data 5
175 Data 1 Data 2 Data 3 Data 4 Data 5
182 Data 1 Data 2 Data 3 Data 4 Data 5


I have, on another spreadsheet a list of ID numbers that I want from the original.

ie

ID
121
123
124
167
187

What I want to do is have excel compare the ID columns, and when it finds a match, to copy all the data in the original row to the new spreadsheet. ie


ID
121 Data 1 Data 2 Data 3 Data 4 Data 5
123 Data 1 Data 2 Data 3 Data 4 Data 5
124 Data 1 Data 2 Data 3 Data 4 Data 5
167 Data 1 Data 2 Data 3 Data 4 Data 5
187 Data 1 Data 2 Data 3 Data 4 Data 5

How would you go about doing this? Vlookup? how could you apply it so that it doesn't just grab the first value of the row and copy over the entire row?

 

[Fazza]

hi, Majus

I'd use a non-formula & non-code approach - a query table. Via menu path ALT-D-D-N & follow the wizard. If you don't have defined names for the data ranges, choose options and then system tables to be able to select the worksheets as data sources. Follow the wizard to the end and choose to edit in MS Query: edit the SQL to something like below.

regards, Fazza

SELECT A.*
FROM `file path & name.xls`.main_source_list A, `file path & name.xls`.filter_list B
WHERE A.ID = B.ID

 

[Majus]

Thank you so much.

I did a bit of fiddling around to get it working, but for anyone else out there with a similar problem, you can use the GUI to specify the table values that you want.

After you have opened the data in Microsoft Query, the interface can be a bit daunting.

Go to alt, b, j.
use the sheet1$.ID column for left and the sheet2$.ID column for right.
then play around with the 3 options to get what you want.
Option 1 was sufficient for what I wanted to do.
Add the query, and then the data table should change to the vaules that you want to select.

Then go to File>Return data to Microsoft Excel and export in a new spreadsheet.

Easy.

Thanks so much for your help, saved me countless hours.

 

[Fazza]