Combining Rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim - 08 Mar 2007 20:56 GMT

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

Reply to this Message

Max - 08 Mar 2007 23:58 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 3 lines]
>
> Help!!!

Reply to this Message

Harlan Grove - 09 Mar 2007 00:52 GMT

Max <demecha...@yahoo.com> wrote...

>Assuming you have source data in cols A to C, from row1 down:
>
[quoted text clipped - 5 lines]
>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:
>
[quoted text clipped - 6 lines]
>=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.

Reply to this Message

Herbert Seidenberg - 09 Mar 2007 15:58 GMT

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

Reply to this Message

Harlan Grove - 09 Mar 2007 17:33 GMT

"Herbert Seidenberg" <herbds7-ms...@yahoo.com> 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?

Reply to this Message

Dave Peterson - 09 Mar 2007 18:14 GMT

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" <herbds7-ms...@yahoo.com> wrote...
> >This could be done without any formulas:
[quoted text clipped - 8 lines]
>
> This requires Excel 2003, doesn't it?

Signature

Dave Peterson

Reply to this Message

Harlan Grove - 09 Mar 2007 22:27 GMT

Dave Peterson <peter...@verizonXSPAM.net> 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.

Reply to this Message

Herbert Seidenberg - 09 Mar 2007 20:43 GMT

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

Reply to this Message