Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiple rows converted to one row
Imported table from DOS database to Excel 2003. Have a worksheet containing
records of 6 columns and 3 rows per record with each record separated by a blank row. Each worksheet contains 4,000-7,000 such records. I need to transpose each record into a single row for sorting purposes. Have tried the Transpose function, but can use that function only on 1 record and 1 column at the time. Iis there a function/formula to transpose that can be repeated through row 7,000 that will apply to 3 rows and 6 columns, then skip a row, then next 3 rows, etc. Can anyone offer suggestion? Thank You! |
#2
|
|||
|
|||
I'm guessing it's a once-off data migration?
I would use a program like TextPad for massaging the data before Excel import... The macro recorder is very nice. That's just me. Excel: Option 1 Put your cursor to top left corner of a record block then start the macro recorder Be sure to set a shortcutkey and click the "Relative Reference" button. Perform your actions then leave your cursor in the top left of the next record block. Run the macro over and over. Option 2 G1: =A2 H1: =B2 .... L1: =F2 M1: =A3 N1: =B3 .... R1: =F3 Highlight G1:R4 Copy Highlight G1:R7000 Paste Highlight G1:R7000 Copy PasteSpecial - Values Sort by row G Scroll Down Delete all the rows which have a blank row G but non-blank row A -- Rob van Gelder - http://www.vangelder.co.nz/excel "Colorado Sherry" <Colorado wrote in message ... Imported table from DOS database to Excel 2003. Have a worksheet containing records of 6 columns and 3 rows per record with each record separated by a blank row. Each worksheet contains 4,000-7,000 such records. I need to transpose each record into a single row for sorting purposes. Have tried the Transpose function, but can use that function only on 1 record and 1 column at the time. Iis there a function/formula to transpose that can be repeated through row 7,000 that will apply to 3 rows and 6 columns, then skip a row, then next 3 rows, etc. Can anyone offer suggestion? Thank You! |
#3
|
|||
|
|||
Correction:
Where I wrote Sort by column G, it should have said Highlight the entire sheet then Sort by column G -- Rob van Gelder - http://www.vangelder.co.nz/excel "Rob van Gelder" wrote in message ... I'm guessing it's a once-off data migration? I would use a program like TextPad for massaging the data before Excel import... The macro recorder is very nice. That's just me. Excel: Option 1 Put your cursor to top left corner of a record block then start the macro recorder Be sure to set a shortcutkey and click the "Relative Reference" button. Perform your actions then leave your cursor in the top left of the next record block. Run the macro over and over. Option 2 G1: =A2 H1: =B2 ... L1: =F2 M1: =A3 N1: =B3 ... R1: =F3 Highlight G1:R4 Copy Highlight G1:R7000 Paste Highlight G1:R7000 Copy PasteSpecial - Values Sort by row G Scroll Down Delete all the rows which have a blank row G but non-blank row A -- Rob van Gelder - http://www.vangelder.co.nz/excel "Colorado Sherry" <Colorado wrote in message ... Imported table from DOS database to Excel 2003. Have a worksheet containing records of 6 columns and 3 rows per record with each record separated by a blank row. Each worksheet contains 4,000-7,000 such records. I need to transpose each record into a single row for sorting purposes. Have tried the Transpose function, but can use that function only on 1 record and 1 column at the time. Iis there a function/formula to transpose that can be repeated through row 7,000 that will apply to 3 rows and 6 columns, then skip a row, then next 3 rows, etc. Can anyone offer suggestion? Thank You! |
#4
|
|||
|
|||
First get rid of the blank rows, Select any column, Edit / Go to / Special /
Blanks, then Edit / Delete / Entire row Now assuming your data on sheet1 starting A1 with no headers, in cell a1 on sheet 2 put the following and copy to A1:R2000 which should cover it =OFFSET(Sheet1!$A$1,((ROW()-1)*3)+(FLOOR(COLUMN()-1,6)/6),(COLUMN()-1)-(FLOO R(COLUMN()-1,6))) Copy whole dataset and paste special as values. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Colorado Sherry" <Colorado wrote in message ... Imported table from DOS database to Excel 2003. Have a worksheet containing records of 6 columns and 3 rows per record with each record separated by a blank row. Each worksheet contains 4,000-7,000 such records. I need to transpose each record into a single row for sorting purposes. Have tried the Transpose function, but can use that function only on 1 record and 1 column at the time. Iis there a function/formula to transpose that can be repeated through row 7,000 that will apply to 3 rows and 6 columns, then skip a row, then next 3 rows, etc. Can anyone offer suggestion? Thank You! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert multiple columns to rows | Excel Worksheet Functions | |||
Multiple rows of data on a single axis (charting) | Charts and Charting in Excel | |||
Can I set a filter for a merged column across multiple rows and o. | Excel Worksheet Functions | |||
Inserting Multiple Rows with Formulas | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |