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!!!

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jim" wrote:
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!!!

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.

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?)

"Herbert Seidenberg" wrote...
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
Select any white cell Find All SHIFT+END Close
....

This requires Excel 2003, doesn't it?

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.



Harlan Grove wrote:

"Herbert Seidenberg" wrote...
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
Select any white cell Find All SHIFT+END Close
...

This requires Excel 2003, doesn't it?

--

Dave Peterson

If blanks or row limits are a problem,
here is an alternate procedu
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

Dave Peterson wrote...
....
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.