Creating Diagonal Matrix from a column of Data

[ShawnStorm]

Good day,

I am trying to create a diagonal matrix from a column data set. I saw this formula online but its not working. Any help would be appreciated. {=IF(A1:A900=TRANSPOSE(A1:A900),A1:A900,"0")}

 

[StephenCrump]

What do you mean by "not working"?

The {} marks indicate an array-entered formula, i.e. entered using the CTRL-SHIFT-ENTER keys instead of just ENTER, which in Excel 365 you don't need to do.

Here's what the formula does:

	A	B	C	D	E	F	G	H	I	J	K	L
1	1
2	2		1	0	0	0	0	0	0	0	0	0
3	3		0	2	0	0	0	0	0	0	0	0
4	4		0	0	3	0	0	0	0	0	0	0
5	5		0	0	0	4	0	0	0	0	0	0
6	6		0	0	0	0	5	0	0	0	0	0
7	7		0	0	0	0	0	6	0	0	0	0
8	8		0	0	0	0	0	0	7	0	0	0
9	9		0	0	0	0	0	0	0	8	0	0
10	10		0	0	0	0	0	0	0	0	9	0
11			0	0	0	0	0	0	0	0	0	10
12
Sheet

Cell Formulas
Range	Formula
C2:L11	C2	=IF(A1:A10=TRANSPOSE(A1:A10),A1:A10,0)
Dynamic array formulas.