Copying and transfering select rows of data

[Guest]

Hi
After 2 hours of trying and sifting through the discussion group I am beaten
enough to ask for help...

I have approx 1000 patient's data in a large spreadsheet recorded
horizontally next to 'ID numbers' in column A.

I have a separate list of 240 or so 'ID numbers', and I'd like them and all
their data to be transfered from the main spreadsheet. I understand the macro
has to run something like (for each of the 240 IDs)

Find 'Patient ID' (in main spreedsheet)
Select Row
Copy row
Activate Sheet 2 (blank spreadsheet)
Paste Row

I can program the last 3 steps but the first two I can't get to work
properly...

Any advice would be VERY much appreciated...

 

[Bernie Deitrick]

Parmenion,

How about this: in cell B2 (assuming your desired ID's start in cell A2) use
the formula:

=VLOOKUP($A2,'Sheet Name'!$A$1:$Z$1000,COLUMN(),FALSE)

Change the Z1000 to reflect the lower left cell of your data table, and
change Sheet Name to reflect the actual sheet name with the data table.

Copy down column 2 to match your IDs in column A, then copy across for as
many columns of data as you need.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thank you! Worked perfectly.

Parmenion,

How about this: in cell B2 (assuming your desired ID's start in cell A2) use
the formula:

=VLOOKUP($A2,'Sheet Name'!$A$1:$Z$1000,COLUMN(),FALSE)

Change the Z1000 to reflect the lower left cell of your data table, and
change Sheet Name to reflect the actual sheet name with the data table.

Copy down column 2 to match your IDs in column A, then copy across for as
many columns of data as you need.

HTH,
Bernie
MS Excel MVP