Forum Discussion
Extracting A Two Column List From the Blanks in a Matrix
Anyone know how to take a matrix where there are some blanks and turn just the blanks into a two column list? Example a. b. c 1. X. X 2. X 3 X. X To spit out: 1 B 2 B 2 C 3 C
Use this for row numbers
=INDEX(IF(ISBLANK($A$1:$C$3),{1;2;3}),SMALL(IF(ISBLANK($A$1:$C$3),ROW($A$1:$A$3)),ROW(1:1)),SMALL(IF(ISBLANK($A$1:$C$3),COLUMN($A:$C)),ROW(1:1)))
Use this for column letters
=INDEX(IF(ISBLANK($A$1:$C$3),{"A","B","C"}),SMALL(IF(ISBLANK($A$1:$C$3),ROW($A$1:$A$3)),ROW(1:1)),SMALL(IF(ISBLANK($A$1:$C$3),COLUMN($A:$C)),ROW(1:1)))
Drag down