Extracting rows based on multiple ranges and unique numbers

**D13** · 03-18-2012, 02:34 PM

I am a noob, so bare with me.

I have a database with roughly 22,000 records. Each row has is a unique record, and contains a column with an identifier number (ID#).

In a separate sheet, I have a large list of these ID#'s that need to have the entire row extracted and copied to another sheet.

Attached is some dummy data. What I am trying to do is.. the ID# column in sheet1, needs to be extracted by the data in column 1 on sheet2 and the results pasted into a new sheet.

I always feel like i am unclear... the numbers from sheet2, are the records i need extracted FROM sheet1.

A friend has created some code for a macro, but it does not run properly. It appears to run correctly but pastes the results over itself, so at the end of running there is only the result of the last record. I can attach that if necessary, or start new.

Thanks!

**mrice** · 03-18-2012, 03:27 PM

Looks like you should be using advanced filter for this task. Have a look at Excel help for more details.

Alternatively, you could use a COUNTIF function in a new column on your first sheet to identify those rows which have a common ID#. Filtering and then copying will give you your selected rows.

**FDibbins** · 03-18-2012, 03:45 PM

Its a bit clumsy, bit it gives you what (I think) you want? Altho, if you have lots of items you are searching for, with 22k rows, it will probably slow things down.

Alternatively, as suggested, maybe filters might work for you, depending on what you are basing your selection on. In 2007, on the "home tab" select "sort and filter", and play around with that and see if that gives you what you want

**D13** · 03-19-2012, 09:33 AM

thanks for the reply,

I have tried the advanced filter, I couldn't get it to cooperate and return the proper results. After several failed attempts more familiar excel users told me it can not be done the way I want, but they could not tell me how. I refuse to believe that it cant be done. When I did use it, it returned the entire database. I will try again.

The list it needs to be sorted by is also large. And will be in a separate sheet. It will always be a large list of non-sequential numbers.

If it helps to understand, this is a database of specimens, I have sent specimens away but only wrote down the ID number, The recipient now needs all the associated data (row)

FDibbins, your solution is correct, but missing the columns to the left of the ID#.

Thanks I will play with this when I have time.