Extracting A Two Column List From the Blanks in a Matrix

[JazzyJeff43]

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

[#2]

@JazzyJeff43 

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

[#3]

@JazzyJeff43 

If Excel 2021 or 365, in F3:

=LET(
    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)

[#4]

@JazzyJeff43 

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