Copying and transfering select rows of data

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...

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




"Parmenion" <[email protected]> wrote in message
news:[email protected]...
> 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...

Thank you! Worked perfectly.

"Bernie Deitrick" wrote:

> 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
>
>
>
>
> "Parmenion" <[email protected]> wrote in message
> news:[email protected]...
> > 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...
>
>
>