Extracting A Two Column List From the Blanks in a Matrix

Occasional Visitor

Anyone know how to take a matrix where there are some blanks and turn just the blanks into a two column list?


    a. b. c
1. X.     X
2. X
3  X. X

To spit out:

1 B
2 B
2 C
3 C

3 Replies


Use this for row numbers



Use this for column letters



Drag down




If Excel 2021 or 365, in F3:

    Names, IF(ISBLANK(Table1), Table1[#Headers]),
    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)


A Power Query option (attached)


Query code (edited):

    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),
    RepeatNames = Table.AddColumn(FilteredTable, "Names", each
        List.Repeat({[Name]}, [NullCount]),
        type list
    NamesToTable = Table.FromColumns({List.Combine(RepeatNames[Names])},
        type table [Blanks=text]