How can I make a matrix style list?

I have data with 2 variables, and a result, organized in 3 columns, and I want a 2 dimensional matrix from this.

Column 1 is a product category 1-999.
Column 2 is a Customer Type A1-Z9
Column 3 is a discount percent.

I want Column 1 to be a Product Category, and Row 1 to be a Customer Type and the resulting data in the appropriate location. Any ideas?

ie:

from:
100 A1 .50
100 A2 .50
100 B1 .45
100 B2 .43
100 C1 .40
100 C2 .40
101 A1 .55
etc...

to:

____A1 _A2 _B1 _B2 _C1 _C2
100 .50 .50 .45 .43 .40 .40
101 .55 .53 .51 .47 .41 .39

(not lined up well here but you get the gist i hope)

etc...


Thanks

Dave

Assuming that the first row contains your headers, and that Columns A, B, and C contain your data...

1) List your customer types across Row 2, starting at F2

2) List your product categories down Column E, starting at E3

3) Enter the following formula in F3, copy down and across:

=SUMPRODUCT(--($A$2:$A$8=$E3),--($B$2:$B$8=F$2),$C$2:$C$8)

Hope this helps!

Originally Posted by dkersten

I have data with 2 variables, and a result, organized in 3 columns, and I want a 2 dimensional matrix from this.

Column 1 is a product category 1-999.
Column 2 is a Customer Type A1-Z9
Column 3 is a discount percent.

I want Column 1 to be a Product Category, and Row 1 to be a Customer Type and the resulting data in the appropriate location. Any ideas?

ie:

from:
100 A1 .50
100 A2 .50
100 B1 .45
100 B2 .43
100 C1 .40
100 C2 .40
101 A1 .55
etc...

to:

____A1 _A2 _B1 _B2 _C1 _C2
100 .50 .50 .45 .43 .40 .40
101 .55 .53 .51 .47 .41 .39

(not lined up well here but you get the gist i hope)

etc...


Thanks

Dave