# Extracting A Two Column List From the Blanks in a Matrix

Occasional Visitor

# 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

3 Replies

# Re: Extracting A Two Column List From the Blanks in a Matrix

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

# Re: Extracting A Two Column List From the Blanks in a Matrix

If Excel 2021 or 365, in F3:

``````=LET(
Rws,   ROWS(Names),
Seq,   SEQUENCE(Rws * COLUMNS(Names),,0),
sqR,   1+MOD(Seq, Rws),
sqC,   1+QUOTIENT(Seq, Rws),
Stack, INDEX(Names, sqR, sqC),
FILTER(Stack, Stack <> FALSE)
)``````

If Excel 365 with Beta function TOCOL, in H3:

``=TOCOL(IF(ISBLANK(Table1),Table1[#Headers],NA()), 2, TRUE)``

# Re: Extracting A Two Column List From the Blanks in a Matrix

A Power Query option (attached)

Query code (edited):

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
UnpivotedColumns = Table.UnpivotOtherColumns(Source, {}, "Name", "Value"),
CountNullByColumn = Table.Group(UnpivotedColumns, {"Name"},
{"NullCount", each Table.RowCount(Source) - Table.RowCount(_), Int64.Type}
),
FilteredTable = Table.SelectRows(CountNullByColumn, each [NullCount] > 0),
List.Repeat({[Name]}, [NullCount]),
type list
),
NamesToTable = Table.FromColumns({List.Combine(RepeatNames[Names])},
type table [Blanks=text]
)
in
NamesToTable``````