×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel - columns to rows

Excel - columns to rows

Excel - columns to rows

(OP)
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

RE: Excel - columns to rows

What excel version?

combo

RE: Excel - columns to rows

(OP)
Office 365

RE: Excel - columns to rows

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

RE: Excel - columns to rows

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. smile
But VBA questions should be asked in forum707: VBA Visual Basic for Applications (Microsoft)


---- Andy

There is a great need for a sarcasm font.

RE: Excel - columns to rows

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...

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
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel - columns to rows

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
DEL 11233 60
should not have converted to
DEL 11233 Product 3 60
but to
DEL 11233 Product 1 60

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close