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

E4:
=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",LOOKUP(2,1/(($A$3:$A$29=E$3)*($B$3:$B$29=$D4)),$C$3:$C$29)))
applied across matrix

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.