excel Transpose Data

[obulldog27]

I have a client who submits lab data in the following excel spreadsheet format. Please see:

http://www.geocities.com/oig01

Need a transpose function or button to automatically change it.

I think? any ideas

 

[foundryqa]

obulldog27


Will the column headers that you list be static or will they change from day to day? Also will the locations listed: Ravenswood and so on, are they static?

Fred

 

[Molby]

I would use a pivot table to create this. Get rid of the blank column b, and fill in column A, so that the names go against each entry, i.e.

Ravenswood   Yeast      $59
Ravenswood   Bacteria   $26
Ravenswood   Lab Chem   $45
Cain         Lab chem   $89
Cain         Lab Serv   $56
etc

You can then create a pivot table which is in the same format as you want.

 

[obulldog27]

foundryqa --- no they will all be dynamic, where x(rows) is the the type of analysis done( Yeast, lab chem ect) and Y(columns) is the name of the wine companies.



Molby --- so from what you showed me on the pivot table I can then sort that format to how I want it, like in the image. ??

 

[Molby]

Yep,

If you head up your columns with something like Name, Type and amount, you can use those in the Pivot tbale wizard.

What you get is an empty table. You can then drag from a list the headings into the Row fields and column fields and the actual data itself.

 

[KenWright]

I would certainly go with Molby's advice on using a Pivot table for this, but we can also help you get your data into database format ready for a Pivot Table first.

ON A COPY of your workbook, and with the data as i see it in the bitmap (jpg would have been better), I would first select the whole of column C, do Edit / Go To / Special / Blanks, then do Edit / Delete / Entire Row. This will ahve gotten rid of the blank rows, but you now need to populate the blank cells in Col A, so select the whole range in Col A that corresponds to the used area in Col B, do Edit / Go To / Special / Blanks, then type = and hit the Up Arrow once and then hit CTRL+ENTER to enter this. You should now see every cell populated with the data from teh cell above. Now copy Col A and then paste special as values to get rid of the formulas. Now go ahead and create your pivot table.

A good intro to pivot tables, (and these things will truly amaze you once you get comfortable with them), is here:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------