Data Consolidation

[Guest]

I have an array of data like the following:

X Y Z
Jan A 3 2 7
Jan C 5 7 3
Jan D 0 1 0

Feb A 5 1 6
Feb B 0 3 3
Feb E 3 3 1

Mar C 4 4 9
Mar D 3 1 9
Mar E 9 0 0

I wish to organize the data like this:

X Y Z
Jan A 3 2 7
Feb A 5 1 6
Mar A 0 0 0 <<No "A" data for march, but still shows as zeroes
Jan B 0 0 0
Feb B 0 3 3
Mar B 0 0 0
Jan C 5 7 3

I have had no luck using a pivot table to do this b/c the data is already in an array format kind of. Basically, I just need to change the array so I can get 12 (months) rows of data for each A, B, C... even if there were some months that had no data.

Any help is greatly appreciated!

-Al

 

[Guest]

From what you wrote, you could simply sort the data by letter then month.

However, you *can* do this with a pivot table. You will need a column heading for months field and another for the letters field. Set these fields as row headings, and X, Y and Z as data. Drag the grey Data button to the column heading area, and you hould have what you want subject to tweaking sub-totals, formats etc.