Formatting (an advanced transpose)

Hey gang,
I am having trouble formatting a dataset. Here is what it looks like:

month.site..size
11.......S3....9
11.......S14...7
11.......S18...3
12.......S2.....4
12.......S15...8

I need to transpose this data to look like this
.................S1 S2 S3 S4 S5....S14 S15 S16 S17 S18
month 11....0...0...9...0...0......... 7....0....0.....0.....3
month 12....0...4...0...0...0........ .0....8....0.....0.....0

I have tried IF THAN statements but it would only correctly place the top "size" properly (eg. 9 in S3) with the rest of the cells having zeros.

Thanks

If you have the data say in Sheet1 starting at A1 and you have listed the months in Sheet2 column A and the sites across the top row... so that the first entry would be in Sheet2, B2, then use formula:

=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1)

copied down and across the matrix.

Hi two_tun and welcome to the forum,

You want your data formatted like a pivot table. See the attached. If you want the data only then you can cut and paste (values only) from the Pivot to another place. Also - you shouldn't have repeated data for the same month and size as it will sum them. I'm thinking you have no repeats.

See the attached.