Transpose and Fill?

[susan]

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!

 

[Pete_UK]

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]

Yes, I do realize I will need new sheets - I can separate the original data
into multiple sheets before running the macro.

 

[susan]

can you or anyone help with the macro? thanks!!!

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

 

[Pete_UK]

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

 

[susan]

sure. thanks pete!

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

 

[Herbert Seidenberg]

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

 

[Herbert Seidenberg]

Automatiker.
Excel 2007 with macro:
http://www.savefile.com/files/1857449