Psychological research

Hi.

I am working on psychological research, and I need your help. Collecting of my data is finished, so it is time to make them look good (and useable). I can't show you my data (because of personal info included), but I will try to write down what I need.
My questionnaire had a few different types of tests. On of them (all made online) exported me results in text form (36 variables, 560 people - that's way too much data to overwrite it manually to individual cells. What I need excel to do (hopefully, it os possible).

1. Find cell containing specific variable name (ex. Age in cell A139).
2. Copy numeral value of that variable in the cell below to chosen cell (ex. copy value from A140 to B20).
3. Find cell containing specific variable name, which is next (ex. Age in cell A687) - yes, there is about 500 rows of another data between two same variables.
4. Copy numeral value of that variable in the cell below to chosen cell (ex. copy value from A688 to B21).
5. Repeat all this till end of the sheet.

So my result will be all "Age" data in cells from B20 to B580, not stretched all the way somewhere in the 270000 rows of data.

Is there any way how to do it?

Thank you

Put this formula in cell B20:

=INDEX(A:A,(ROWS($1:1)-1)*548+140)

then copy down into B21, B22 etc. as far as you need to. It will return the equivalent of A140, A688, A1236 etc. in successive cells.

Hope this helps.

Pete

Thank you very much, but number of other data between 2 same variables is not always the same - sometimes it is 480, sometimes 520.. there is no pattern in this...

Thank you... I am sending data sample.. there are 3 responses (about 1800 rows).. In collum A there are raw data (begining of next response is
marked). There are also sample results for one variable.. I hope, it is understandable.

Thanks

Try this ...

=IFERROR(INDEX(A:A,SMALL(IF($A$1:$A$270000=$K$1,ROW($A$2:$A$270001)),ROWS($K$1:K1)*2-1)),"")

Enter with Ctrl+Shift+Enter.

Try

Insert new row 1 (blank)

in B2

=IF(AND(A2=$K$2,ISNUMBER(A3+0)),MAX($B$1:B1)+1,"")

This finds all occurences of K2 value which are followed by number in next row

Copy down list

in K3

=INDEX($A$2:$A$5000,MATCH(ROWS($1:1),$B$2:$B$5000,0)+1)

Copy down

Try

Data starting in row 2

=IFERROR(INDEX($A$3:$A$27001,SMALL(IF($A$2:$A$27000=$K$2,IF(ISNUMBER($A$3:$A$27001+0),ROW($A$3:$A$27001)-ROW($A$3)+1,""),""),ROWS($A$3:A3))),"")

Or

=IFERROR(INDEX($A$3:$A$5001,SMALL(IF(($A$2:$A$5000=$K$2)*(ISNUMBER($A$3:$A$5001+0)),ROW($A$3:$A$5001)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

Enter with Ctrl+Shift+Enter