1

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?

Improve this question
4
  • You could write a macro to do it.
    – Raystafarian
    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?
    – Excellll
    Aug 18, 2015 at 17:40
  • I think Raystafarian is right, I just have never created a macro.
    – amy
    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.
    – amy
    Aug 19, 2015 at 17:56

2 Answers 2

Reset to default
0

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.

Improve this answer
2
  • Thank you, Scott, but this is not what I am looking for. All the data is in one workbook, but with a different tab for each month. Each month has between 40-120k rows of data. There is no analysis sheet yet--that is what I am trying to create. I think a macro that lets me paste in the rows needed from each tab may work.
    – amy
    Aug 19, 2015 at 18:05
  • I don't understand what about my answer is not what you are looking for.  In my answer, I'm telling you how to create the analysis sheet, and I'm using column P to identify the tab that each row is coming from.  As an example, I showed how to designate that rows 10847, 10222, 12387, and 3368 (the first four row numbers from the list in your question) should be pulled from sheet (i.e., tab) Aug13, followed by rows 17, 42, 83, and 95 from sheet Sep13.
    – Scott - Слава Україні
    Aug 19, 2015 at 19:15
0

You can use the 'data filter' to enable the selection of the specific criteria you want. The first row will change to selection from drop down menu. After you click, the content will only show the ones you are interested, although the row numbers are not continuous yet. Now you can copy all these selected rows/cells to another excel worksheet, the row numbers are continuous in the new worksheet. You have only the rows you want. If you use the 'data advanced filter', it takes longer to input data source. I think the data filter, select, copy and paste is easier to create what you need.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .