Forum Discussion

JazzyJeff43's avatar
JazzyJeff43
Copper Contributor
Jun 22, 2022

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

excel
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 22, 2022

    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

     

     

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 22, 2022

    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)
  • Leal_D's avatar
    Leal_D
    Copper Contributor
    Jun 22, 2022

    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

Resources