Combining Rows
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!!! |
Combining Rows
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!!! |
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. |
Combining Rows
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?) |
Combining Rows
"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? |
Combining Rows
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 |
Combining Rows
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 |
Combining Rows
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. |
All times are GMT +1. The time now is 08:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com