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 

 

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 https://support.microsoft.com/en-au/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed, in H3:

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

Resources