Transpose question

[khaos]

Hi Guys,

I have a problem with transposing. I have attached the spread sheet below. basically I have some companies with number of members per year running in rows.. ie

here us how it looks now :

Currently :
A _________ B___C
Company1 2001 5
Company1 2003 4
Company2 2001 5
Company2 2002 6
Company2 2003 7
Company2 2004 20
Company2 2005 25



Here is how I want it to look after

___A ________B _____C _____ D _____ E ______ F
_______ 2001____2002____2003____2004____2005
Company1 5 _______ 4
Company2 5 _______6______ 7______20______25

I'm just using underscores to denote spacing since it wont allow me to add too many spaces


I got about 100 companies and any help would be greatly appreciated!!

Thanks

[Mandy11]

I created a pivot table chart. A pivot table is primarily used to summarize data, which includes a mathematical formula, such as adding, counting, etc...

I kinda tricked it so it would "show" the data, not add it. But, you will run into a problem if the same company for the same year has the exact same data.

As you can see from the attached, I added an additional data line of:

Add-Awn Pit Shade 2003 33

When I added this data, the pivot table, added the data; therefore, showing it as 66.

This solution may not work for you, but I thought it might help.

[khaos]

Oh thanks this is pretty awesome. How do you add more companies to this sheet, Im not very familiar with pivot tables

Thanks!

[khaos]

Awesome thanks figured out how to do this.

For anyone who needs to do this. Any additional rows with different values, just recreate the pivot table with the full ranges.

If there is a better way feel free to post up!

Thanks

[robert111]

I was working on a SUMPRODUCT solution when I saw the above reply

......A........B.......C
1..alpha...2004....77
2..alpha...2005....88
3..beta....2004....213
4..beta....2005....246

......E........F........G
1...........2004....2005
2..alpha...77........88
3..beta....213.....246

in cell F2 put the formula
=sumproduct(($a$1:$a$4=$e$2)*($B$1:$b$4=$f$1)*($c$1:$c$4))

this gives 77

you can put 4000 instead of 4, and that way you will pick up newly added data without having to add to the ranges and remember to refresh a pivot table.

[khaos]

ah thanks actually I was wondering if there was a way to do it with a formula too. I think this would help me out just as much.

Thanks!

[Mandy11]

Usually when I set up a pivot table, I have the data in one sheet and put the pivot table in its own sheet. When I create the pivot table, I select the entire sheet as the data range (by clicking on the box above row 1 and to the left of column A <the gray box>, this selects the entire sheet. This allows you to add rows and columns to the data without having to update the range.

Then all you have to do is right click within the pivot table and click refresh and then it refreshes the pivot table automatically.

You can set the pivot table to automatically refresh when the file is opened or closed, but the file will become very large when you do that. It is best to just right click on the pivot table and manually refresh it.

Have a great day.