Combining Rows

[Guest]

I have a spreadsheet where the data I have downloaded went into two rows,
three columns in row 1 and 3 columns in row 2. How can I move the data from
row 2 columns A, B, and C to row 1 columns D, E, and F? I have over 600 of
thse to do, so I wanted to find something automated, so I wouldn't have to
cut and paste each one.

Help!!!

 

[Guest]

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

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))
Copy D1 across by 6 cols to I1, then fill down as far as required to exhaust
the source data in cols A to C. This returns the required transformation in
cols D to I. Copy cols D to I & paste special as values elsewhere as desired.

 

[Harlan Grove]

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$11)-
(COLUMNS($D11)<4),MOD(COLUMNS($D11)-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.

 

[Herbert Seidenberg]

This could be done without any formulas:
Select cells plus top blank row.
Format > AutoFormat > List1
Options > Uncheck all except Pattern
Edit > Find > Options > Format > Choose Format from Cell

Select any white cell > Find All > SHIFT+END > Close

Insert > Shift cells right
Select all
Edit > Go To > Special > Blanks
Delete > Shift cells up

This assumes there are no blanks in the data.
A certain row limit exists.(Why?)

 

[Harlan Grove]

This could be done without any formulas:
Select cells plus top blank row.

....

So OP may need to insert a blank row above the data.

Edit > Find > Options > Format > Choose Format from Cell

....

This requires Excel 2003, doesn't it?

 

[Dave Peterson]

Data|List was added in xl2003. (But the Format|autoformat has been there pretty
long, IIRC.)

The enhancement to Edit|Find based on format was added in xl2002.

 

[Herbert Seidenberg]

If blanks or row limits are a problem,
here is an alternate procedure:
Select cells plus top blank row
Format > AutoFormat > List1
Options > Uncheck all except Pattern
Edit > Find > Options > Format > Choose Format from Cell

Select any white cell > Find All > SHIFT+END > Close

Copy > Paste to the right of data, up 1 row
Select left half of data
Find > Find All > SHIFT+END > Close
Delete > Shift cells up

 

[Harlan Grove]

The enhancement to Edit|Find based on format was added in xl2002.

....

Thanks for that. 2002 was the one version I didn't use for any
appreciable period.