Find text in 1 col, copy to output col A, drop 5 rows, copy to output col B, drop 8, etc

[charlieepes]

I have a very specific need: Creating a table of data from a single column of data. 

Find text in a column, copy/paste, then dropping x rows down, copy/paste, then dropping x rows down, copy/paste, etc. I have one column of data A:A

Input is Excel Sheet1:

Sheet1: Find text "MyText", copy "MyText" into Sheet2, A2
Sheet1: from "MyText", drop 5 rows, copy into Sheet2, B2
Sheet1: from "MyText", drop 8 rows, copy into Sheet2, C2
Sheet1: from "MyText", drop 16 rows, copy into Sheet2, D2

Then loop to find the next "MyText" and paste into A3, and so on.

Thanks

[Ben_H · Accepted solution]

Hi @charlieepes,

This is probably somewhat simplified but I think this does what you want -

1. I made a look up table that controls which rows get returned.

2. I then join the main list (after adding a recordID) to this lookup table, and then calculate the rows which correspond to your example  (0,5,8,16) by adding the RecordID to my Rows column.

3. Next join the full list record ID to the calculated rows field.

4. Create a running total and cross tab.

Hopefully that works for you.

Regards,

Ben

[charlieepes]

Thank you Ben!  Simple approach is good-

Charlie