How can I pull specific rows in Excel into a new spreadsheet

I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample. Is there a formula I can use to input my row numbers and have them pull specific rows into a new sheet so I don't have to copy and paste 1000 rows from 10 different sheets? For example, from my Aug13 sheet I need to code rows: 10847, 10222, 12387, 3368, 9476, 10627, 3296, 13400, 9867, 6846 and so on. Do I have to manually copy each row?

asked Aug 18, 2015 at 14:18 11 1 1 gold badge 1 1 silver badge 3 3 bronze badges You could write a macro to do it. Commented Aug 18, 2015 at 16:18

Just to clarify, the records you need to copy are identified by row number and not by an assigned ID?

Commented Aug 18, 2015 at 17:40 I think Raystafarian is right, I just have never created a macro. Commented Aug 19, 2015 at 17:54

To Excellll, yes they are by row number but there is no reason I can't add a column and give each row an ID number too.

Commented Aug 19, 2015 at 17:56

2 Answers 2

Your question is a little vague on the "I have several spreadsheets containing data I need to analyze" aspect of this. I'll assume that you have something like this:

Aug13 10847 Aug13 10222 Aug13 12387 Aug13 3368 Sep13 17 Sep13 42 Sep13 83 Sep13 95

in columns P and Q of your Analysis sheet.

If that's close, then put

=OFFSET(INDIRECT($P1&"!$A$1"), $Q1-1, COLUMN()-1)

into Analysis!A1 , and drag down and to the right, far enough to get all the data. $P1&"!$A$1" builds a string (text value) that looks like Aug13!$A$1 . INDIRECT($P1&"!$A$1") turns that string into an actual reference to cell Aug13!$A$1 . And then =OFFSET(…, $Q1-1, COLUMN()-1) moves $Q1-1 rows down from there and COLUMN()-1 columns to the right. Using COLUMN() is the easiest way to get the formula to drag to the right correctly.

Note that we use -1 because these are offsets from A1 . For example, to access row 17 relative to row 1, you need to go down 16 rows.

To make this permanent, just copy and paste values.