Excel - columns to rows 1

[rk68]

Hi,
I have an excel sheet having data in the below format.
City ID Product 1 Product 2 Product 3
MUM 1234 45
MUM 2345 65 12
MUM 3456 12 24 48
MUM 4567 12
MUM 5678 12 24
DEL 6789 48 36
DEL 7900 12 24 36
DEL 9011 36
DEL 10122 48
DEL 11233 60

The columns City & ID will remain. Product 1 , Product 2 & Product 3 are the columns which are to be moved as rows.
Please see the format below. The data should be in the format given below.
I there a formula/script by which I can get the data in the below format. I have above 12 files with around 3-4 lacs records in each file. I have attached the file for your reference.
City ID Products Qty
MUM 1234 Product 1 45
MUM 3456 Product 1 12
MUM 5678 Product 1 12
DEL 7900 Product 1 12
DEL 9011 Product 1 36
MUM 2345 Product 2 65
MUM 3456 Product 2 24
MUM 5678 Product 2 24
DEL 6789 Product 2 48
DEL 7900 Product 2 24
DEL 10122 Product 2 48
MUM 2345 Product 3 12
MUM 3456 Product 3 48
MUM 4567 Product 3 12
DEL 6789 Product 3 36
DEL 7900 Product 3 36
DEL 11233 Product 3 60

TIA,
Raj

 

[combo]

What excel version?

combo

 

[rk68]

Office 365

 

[combo]

Go to "Data" in ribbon from Get&Transform section, query from table. You will be asked to create table if your data are in plain worksheet, confirm (I know that MS slightly modified data access in office 365, hope you will find it). You should see your data in power query desktop.
Select "City" and "ID" columns. In the "Transform" ribbon select "Unpivot other columns" (after drop-down "Unpivot columns"). You should get required layout.
Double-click any header and rename it if required. Note that excel records all your actions (right pane).
If the data match your needs, from "File", "Close&Load", select "Table" as destination and new or existing sheet.

Alternatively normalize your table using pivot table (

http://www.tek-tips.com/faqs.cfm?fid=5287)

combo

 

[Andrzejek]

You may also investigate doing all of it in VBA code. It is a lot of fun and not that difficult, at least for me.
But VBA questions should be asked in forum707


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Rk68,

You’ve been knocking around here for over 15 years. There’s TGML and icons above the Reply window, so that your posted example might display in a more meaningful way with some additional TLC...

[pre]
City ID Product 1 Product 2 Product 3
MUM 1234 45
MUM 2345 65 12
MUM 3456 12 24 48
MUM 4567 12
MUM 5678 12 24
DEL 6789 48 36
DEL 7900 12 24 36
DEL 9011 36
DEL 10122 48
DEL 11233 60
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Deniall]

And if you had presented your data in a user-friendly manner, it would have turned out also to have been you-friendly as well.[ ] You might have noticed that your final line item
[tt]DEL 11233 60[/tt]
should not have converted to
DEL 11233 Product 3 60
but to
DEL 11233 Product 1 60