| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose and Fill?
Original data looks like this in Columns:
Name Dept. Location Ext. XXX 101,102,103,104 111 7777 YYY 106,107,108 222 8888 ZZZ 210,220,230 333 9999 I want the data to look like this: Name Dept. Location Ext. XXX 101 111 7777 XXX 102 111 7777 XXX 103 111 7777 XXX 104 111 7777 YYY 106 222 8888 YYY 107 222 8888 YYY 108 222 8888 ZZZ 210 333 9999 ZZZ 220 333 9999 ZZZ 230 333 9999 I used the "Text to Columns" function to separate the "Dept" into multiple columns. I want to transpose the "Dept" column to rows following the original row, then copy the rest of the information from the original row to the transposed rows. Is this possible? I have 2 excel tabs with 52,000 records in each right now so I can't imagine doing this manually. I am using Excel 2003. Please HELP!!!! Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose and Fill?
In the example your 3 starting rows become 10 rows. So, if you have 52,000
of them you are likely to end up needing another sheet (or more) to accommodate the extra rows produced. Consequently you will need a macro solution, as a formula could not automatically insert new sheets. You will need to advise on how you want to treat the excess rows. Pete "susan" wrote in message ... Original data looks like this in Columns: Name Dept. Location Ext. XXX 101,102,103,104 111 7777 YYY 106,107,108 222 8888 ZZZ 210,220,230 333 9999 I want the data to look like this: Name Dept. Location Ext. XXX 101 111 7777 XXX 102 111 7777 XXX 103 111 7777 XXX 104 111 7777 YYY 106 222 8888 YYY 107 222 8888 YYY 108 222 8888 ZZZ 210 333 9999 ZZZ 220 333 9999 ZZZ 230 333 9999 I used the "Text to Columns" function to separate the "Dept" into multiple columns. I want to transpose the "Dept" column to rows following the original row, then copy the rest of the information from the original row to the transposed rows. Is this possible? I have 2 excel tabs with 52,000 records in each right now so I can't imagine doing this manually. I am using Excel 2003. Please HELP!!!! Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose and Fill?
Yes, I do realize I will need new sheets - I can separate the original data
into multiple sheets before running the macro. "Pete_UK" wrote: In the example your 3 starting rows become 10 rows. So, if you have 52,000 of them you are likely to end up needing another sheet (or more) to accommodate the extra rows produced. Consequently you will need a macro solution, as a formula could not automatically insert new sheets. You will need to advise on how you want to treat the excess rows. Pete "susan" wrote in message ... Original data looks like this in Columns: Name Dept. Location Ext. XXX 101,102,103,104 111 7777 YYY 106,107,108 222 8888 ZZZ 210,220,230 333 9999 I want the data to look like this: Name Dept. Location Ext. XXX 101 111 7777 XXX 102 111 7777 XXX 103 111 7777 XXX 104 111 7777 YYY 106 222 8888 YYY 107 222 8888 YYY 108 222 8888 ZZZ 210 333 9999 ZZZ 220 333 9999 ZZZ 230 333 9999 I used the "Text to Columns" function to separate the "Dept" into multiple columns. I want to transpose the "Dept" column to rows following the original row, then copy the rest of the information from the original row to the transposed rows. Is this possible? I have 2 excel tabs with 52,000 records in each right now so I can't imagine doing this manually. I am using Excel 2003. Please HELP!!!! Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose and Fill?
can you or anyone help with the macro? thanks!!!
"Pete_UK" wrote: In the example your 3 starting rows become 10 rows. So, if you have 52,000 of them you are likely to end up needing another sheet (or more) to accommodate the extra rows produced. Consequently you will need a macro solution, as a formula could not automatically insert new sheets. You will need to advise on how you want to treat the excess rows. Pete "susan" wrote in message ... Original data looks like this in Columns: Name Dept. Location Ext. XXX 101,102,103,104 111 7777 YYY 106,107,108 222 8888 ZZZ 210,220,230 333 9999 I want the data to look like this: Name Dept. Location Ext. XXX 101 111 7777 XXX 102 111 7777 XXX 103 111 7777 XXX 104 111 7777 YYY 106 222 8888 YYY 107 222 8888 YYY 108 222 8888 ZZZ 210 333 9999 ZZZ 220 333 9999 ZZZ 230 333 9999 I used the "Text to Columns" function to separate the "Dept" into multiple columns. I want to transpose the "Dept" column to rows following the original row, then copy the rest of the information from the original row to the transposed rows. Is this possible? I have 2 excel tabs with 52,000 records in each right now so I can't imagine doing this manually. I am using Excel 2003. Please HELP!!!! Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose and Fill?
Sorry, Susan, I was just making some (fairly obvious) observations. I
don't have time at the moment to put a macro together for this. If you do not get any help on this thread, perhaps you could re-post in the .programming group. Pete On Oct 23, 4:21*pm, susan wrote: can you or anyone help with the macro? thanks!!! "Pete_UK" wrote: In the example your 3 starting rows become 10 rows. So, if you have 52,000 of them you are likely to end up needing another sheet (or more) to accommodate the extra rows produced. Consequently you will need a macro solution, as a formula could not automatically insert new sheets. You will need to advise on how you want to treat the excess rows. Pete "susan" wrote in message ... Original data looks like this in Columns: Name * * Dept. * * * * * * * * * * Location * * * Ext. XXX * * * 101,102,103,104 * * * 111 * * * * * 7777 YYY * * * *106,107,108 * * * * * * 222 * * * * * 8888 ZZZ * * * *210,220,230 * * * * * * 333 * * * * * 9999 I want the data to look like this: Name * * Dept. * * Location * * * *Ext. XXX * * * 101 * * * * * 111 * * * * *7777 XXX * * * 102 * * * * * 111 * * * * *7777 XXX * * * 103 * * * * * 111 * * * * *7777 XXX * * * 104 * * * * * 111 * * * * *7777 YYY * * * *106 * * * * * 222 * * * * *8888 YYY * * * *107 * * * * * 222 * * * * *8888 YYY * * * *108 * * * * * 222 * * * * *8888 ZZZ * * * *210 * * * * * 333 * * * * *9999 ZZZ * * * *220 * * * * * 333 * * * * *9999 ZZZ * * * *230 * * * * * 333 * * * * *9999 I used the "Text to Columns" function to separate the "Dept" into multiple columns. *I want to transpose the "Dept" column to rows following the original row, then copy the rest of the information from the original row to the transposed rows. *Is this possible? I have 2 excel tabs with 52,000 records in each right now so I can't imagine doing this manually. *I am using Excel 2003. Please HELP!!!! Thanks!- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose and Fill?
sure. thanks pete!
"Pete_UK" wrote: Sorry, Susan, I was just making some (fairly obvious) observations. I don't have time at the moment to put a macro together for this. If you do not get any help on this thread, perhaps you could re-post in the .programming group. Pete On Oct 23, 4:21 pm, susan wrote: can you or anyone help with the macro? thanks!!! "Pete_UK" wrote: In the example your 3 starting rows become 10 rows. So, if you have 52,000 of them you are likely to end up needing another sheet (or more) to accommodate the extra rows produced. Consequently you will need a macro solution, as a formula could not automatically insert new sheets. You will need to advise on how you want to treat the excess rows. Pete "susan" wrote in message ... Original data looks like this in Columns: Name Dept. Location Ext. XXX 101,102,103,104 111 7777 YYY 106,107,108 222 8888 ZZZ 210,220,230 333 9999 I want the data to look like this: Name Dept. Location Ext. XXX 101 111 7777 XXX 102 111 7777 XXX 103 111 7777 XXX 104 111 7777 YYY 106 222 8888 YYY 107 222 8888 YYY 108 222 8888 ZZZ 210 333 9999 ZZZ 220 333 9999 ZZZ 230 333 9999 I used the "Text to Columns" function to separate the "Dept" into multiple columns. I want to transpose the "Dept" column to rows following the original row, then copy the rest of the information from the original row to the transposed rows. Is this possible? I have 2 excel tabs with 52,000 records in each right now so I can't imagine doing this manually. I am using Excel 2003. Please HELP!!!! Thanks!- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose and Fill?
Here is a semi-automatic way with
Excel 2007 and Reverse Pivot Table. Excel 2007 has enough space. One easy formula and no code. Step by step instructions: http://www.savefile.com/files/1855120 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Transpose and Fill?
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I transpose reference cells by Auto-fill? | Excel Discussion (Misc queries) | |||
| Fill in form to type Item descrictions and costs and fill in funct | Excel Worksheet Functions | |||
| I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
| I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) | |||
| The fill feature in Excel that gives option to fill or copy | New Users to Excel | |||
Linear Mode
