Can't Use Pivot Table Properly?

**jdomante** · 11-05-2009, 10:36 AM

I've tried to do this with Pivot Tables and the formatting come out correctly however the table makes you display the values as a type of formula and not just the variable itself.

I could manually put the variables into this format I need but there are about 500 companies I have so it would take a long time. Is there any way of doing this in excel?

**DonkeyOte** · 11-05-2009, 10:55 AM

Which approach you can adopt depends in part on a few factors ie

Is your data always sorted by Company (alphabetically) ?

Will you always have a record for each combination of Company & Month ?

Regardless you should first alter your headers in E3 onwards such that they are valid dates, eg enter 1/1/9 (custom format to mmm if that's preferred aesthetic) thereafter one approach assuming unsorted data but record per combination:

E4: =LOOKUP(2,1/(($A$3:$A$29=E$3)*($B$3:$B$29=$D4)),$C$3:$C$29)
applied across matrix

If data is sorted by company and subsequently month and there is always one record per combination

E4: =INDEX($C:$C,MATCH($D4,$B:$B,0)+(MONTH(E$3)-1))
which would most likely prove more efficient long term

**jdomante** · 11-06-2009, 12:33 PM

Thanks for your response the Index works however there are some records that do not have a value for each month. Is there any way to do it if this is the case?

**DonkeyOte** · 11-06-2009, 12:36 PM

In this case I would probably opt for

Please Login or Register  to view this content.

Depending upon the quantity of info in your "real" file (and subsequent matrix dimensions) it may be worth considering use of helpers to generate the matrix.