helps matrix table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mycarpark@gmail.com - 15 Jun 2006 11:53 GMT

--------------------------------------------------------------------------------
excel question

if i have this table (two col, several rows)

x 1
x 2
x 1
y 1
z 2
y 1
z 2

i want to display in matrix table in excel
x y z
1 2 2 2
2 1 2 1

how to write simple function to display above?
e.g. 1 and x is totally display 2 times.

Reply to this Message

Bondi - 15 Jun 2006 12:18 GMT

> --------------------------------------------------------------------------------
> excel question
[quoted text clipped - 16 lines]
> how to write simple function to display above?
> e.g. 1 and x is totally display 2 times.

Hi,
If you set up your matrix in say C1 so that D1=X, E1=Y and F1=Z and
C2=1 and C3 = 2.
Asuming your data is in A1:B7 then you could use SumProduct().

=SUMPRODUCT(--($A$1:$A$7=D1),--($B$1:$B$7=$C$2))

Place the formula in D2 and copy out along the row. Careful with the
cell referencing.

Regards,
Bondi

Reply to this Message