How to find data & offset

**jeffko** · 04-06-2007, 11:09 PM

OK, my problem is probably very simple, but i just can't seem to figure it out. I import 13 .csv files into one excel workbook, each into a separate sheet. In each .csv file there is the word "FINALS", which will always be in column A, but never the same row. I need to locate the word "FINALS" and then return the data in the following two rows below it (across columns A-L). The 14th sheet (named results) will gather the data i have collected from each of the 13 sheets.

HOW?

Thanks for any help.
jeff.

**BigBas** · 04-06-2007, 11:32 PM

Without seeing your data, it is hard to determine. You can use a setup like this:

=offset(index(A:A,match("finals",A:A,0),row()-row(A1),column()))

If you enter that formula into cell A1, then drag it down one row, and across as many rows as necessary, it will pull the info from one row and two rows respectively below the match.

Let me know if it works,

**Paul** · 04-06-2007, 11:41 PM

I tried that, BigBas, at least on my example data, and it didn't work properly (actually returns an error, I think a parenthesis might be missing in the middle).

I came up with a different formula, if you look at my example sheet (attached). Is this anything like your setup?

Please Login or Register  to view this content.

oops, formula for second row of each sheet is ".......+2, COLUMN..."

Please Login or Register  to view this content.

These formulas would capture both rows of data for sheet1 (if you drag-filled it across to column L). Then you could select both rows and fill it down for the other 12 sheets (24 rows) and just replace the Sheet1 with Sheet2, Sheet3, etc. (Edit-Replace works quickly for that.)

**Paul** · 04-06-2007, 11:51 PM

PS --- change your thread title before the 'powers that be' lock this thread on you. It should be something descriptive of your issue. (Luckily, I think all the mods are drunk or on holiday!)

**jeffko** · 04-07-2007, 12:10 AM

thanks for the help so far guys...i should have sent the file - here it is.

i fixed the title too!

paul is pretty close, i think having the actual file will help.

let me know what you can do...

thanks again.

**Paul** · 04-07-2007, 12:29 AM

Check out your results sheet..

Because your data isn't contiguous in your data sheets, the formula needed modifying as it goes across columns in each row, but I think I have it sorted out.

I modified my formula to work in cell B4, and modified my 2nd row formula to work in B5. Then I dragged it across three columns (because those three results columns are contiguous on the data sheets). I then re-modified the formulas to add more columns to the right when searching (+4 instead of +3 at the end) because there's a gap in the data sheets to where the cells are that you want to pull into the results sheet.

Once I had those two rows of data, I simply copied B4:H5, selected B8:H9 and PasteSpecial->Formulas. I did the same for all of the other results too.

I then used Edit-Replace to replace the occurrences of 'SP DT' in the formulas for B8:H9 to 'PRO DT'. I'll let you modify the sheet refs for the other results sections, since it's pretty easy to do.

Let me know if you need anything else.

PS - You may get blanks or errors for all of the sheets past 'SPORTSMAN DT', since the example you posted had no data in those sheets.