Excel transpose

hi EE,

I have thos columns in excel :

Style      Model Code      Description      Scale Description      Sizes      QTY
A123         1123                TEST A                  INCHES               44           7
A123         1123                TEST A                  INCHES               46           3
A123         1123                TEST A                  INCHES               50           1
A123         1123                TEST A                  INCHES               52           17

B777         3411                TEST B                  REG                     R32           2
B777         3411                TEST B                  REG                     R34           37

is there a way in excel to transpose this for the sizes to be across left to right instead of up to down ?  like this :

A123         1123                TEST A                  INCHES                           44              46              50                52
                                                                                                               7                3               1                  17

B777         3411                TEST B                  REG                                R32            R34
                                                                                                              2                 37

let me know.. .?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David EisenbeiszCEOCommented:
There is the transpose function, but it won't add new lines and make the other changes you need.  You will probably have to write a vba sub to do what you are asking.
Matt NicholasBusiness AnalystCommented:
You are going to love this feature called "Power Query" or "Get and Transform" by Microsoft. This job will be done in 2 minutes flat with no code!

First, highlight your data.
Second, locate the 'insert' tab and tick the 'my table has headers box'
Third, locate the Data tab and find the "Get and transform" function. Choose the 'From Table' data source option
Fourth, highlight all columns from "Style" to "Scale Description"
Fifth, Right click and select "unpivot other columns"
Sixth, click "Close and Load" in the top left and enjoy!

Power query will apply the steps above repetitively to any dataset you add to the original table in future also, think of it as VBA without code!

Try google this if you need more information



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsExcel & VBA ExpertCommented:
Hi Phillippe,

This can also be achieved with Pivot Table:
  1. Add Style, Model Code, Description, Scale Description in Row Labels.
  2. Add Sizes in Column Labels.
  3. Add Sum of Qty in Values.
  4. Click on any cells in Pivot Table.
  5. Navigate to Design
  6. Subtotals: Do Not Show Subtotals
  7. Grand Totals: Off for Rows and Columns
  8. Report Layout: Show in Tabular Form
Check attached for your reference.
Hope this helps...
ShumsExcel & VBA ExpertCommented:
No Comments added further
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.