Combining Rows
Max wrote...
Assuming you have source data in cols A to C, from row1 down:
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15
16 17 18
etc
I'll just assume the data is in a range named D.
and you want it transformed into 6 cols "pair-wise", ie into:
1 2 3 4 5 6
7 8 9 10 11 12
13 14 15 16 17 18
etc
Just place in D1:
=OFFSET(INDIRECT("A"&ROW(A1)*2-1),INT((COLUMN(A1)-1)/3),
MOD(COLUMN(A1)-1,3))
OFFSET and INDIRECT?
Without volatile functions,
D1:
=INDEX(D,2*ROWS(D$1:D1)-
(COLUMNS($D1:D1)<4),MOD(COLUMNS($D1:D1)-1,3)+1)
Fill D1 right into E1:I1, then fill D1:I1 down as far as needed.
But this could be done with simpler formulas and an autofilter. Enter
the formula =A2 in cell D1 and fill D1 right into E1:F1. Enter the
formula =MOD(ROWS(G$1:G1),2) in cell G1. Select D1:G1 and double click
on the fill handle (the box at the bottom right corner of the border
around the selected range). Assuming this filled D1:G1 down into
D2:G700, D1:G700 should now be the selected range. Run the menu
commands Edit Copy then Edit Paste Special as values. Then select
A1:G700, run the menu command Data Filter AutoFilter, filter
column G for value 0, delete the filtered rows from row *2* down,
clear the autofilter by running the menu command Data Filter
AutoFilter again, and clear the range of 1s in column G.
|