Moving sets of rows into a single row

Try this

Formula:

Please Login or Register  to view this content.

Regards

Sorry for post #2
Try this
=OFFSET($A$1,2+3*QUOTIENT((COLUMN(A1)-1),3),MOD(COLUMN(A1)-1,3))

Formula:

Please Login or Register  to view this content.

Regards

Originally Posted by JohnTopley

Is the result a single row?

I'd like all the results in a single row, yes...that's kind of the whole thing, is moving sections of several rows into a single row, while eliminating the empty cells. Like concatenating, but instead of combining cells into one, just added cells onto a growing row.

Hi
Solution 1: Use the F column (Sheet1!F2:F30)
with this array formula in F2:

Formula:

Please Login or Register  to view this content.

and copy it to Sheet1!F3:F30
Place the following formula in Sheet2!A4 and copy it to Sheet2!B4:T4

Formula:

Please Login or Register  to view this content.

Solution 2: Place the following array formula in Sheet2!A6 and copy it to Sheet2!B6:T6

Formula:

Please Login or Register  to view this content.

Note: 5 is the same as COLUMNS(Sheet1!$A$2:$E$30)
See the file ManyRows to OneRow.xlsx
Best regards

Thanks, guys. I was able to modify both of your solutions to work for what I need.

To help me learn (and because I can see this being useful in the future), is there a simple modification to do a similar process, where a table of 6 columns with many empty rows is returned to a new table of 6 columns, but not returning the empty rows? I feel like either one of these should be easily changed to accomplish just that, but I'm not able to make it work.

try

enter in A2 of Sheet2 of the file you posted


=IFERROR(INDEX(Sheet1!A$2:A$100,SMALL(IF(ISTEXT(Sheet1!$A$2:$A$100),ROW($A$2:$A$100)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl + Shift + Enter

Copy across as many columns as needed and the copy down