I have this scenario where i want to extract a subset of a range in one row (using index, match and row functions preferably ) and return only smaller filtered array with cells that contain text/data and exclude empty cells .. I want to use this filtered array for further processing

The input 2-D array i want to get a subset array from is colored

The image shows the returned array size in last column

A dropdown menu is provided for the user to select the desired row (say A1:A6).

The row selected by the user determines which row of the 2-D colored range (say B1:G6 ) to extract the filtered array from then only cells with text/data are returned from that row

I tried the following code

I can get the array of whole correct row (all cells) using

index(B1:G6, match(A1:A6,value_from_dropdown_menue,0),0)

then i tried to apply row() function to filter out empty elements from that returned array .. lets refer to the returned_full_row_array as RFRA for readability

index(RFRA, row(indirect("1:"& counta(RFRA))))

the full formula looks like this (B8 is the cell with dropdown )

=INDEX(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0),ROW(indirect("1:"& counta(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0)))))

but the row() function is not returning an array and thus i don't any array returned .. i only get the first element.. even if i hardcoded row(1:3) .. i don't get 3 element array back ... only the first element of the the whole row

any ideas?

Edit : I can successfully get the whole row of my range using index match the result is a fixed size 1 by 6 array like this (example first and 2nd row ):

for 1st row >> {"M1-item1",0,0,0,0,0}
for 2nd row >> {"M2-item1","M2-item2","M2-item3","M2-item4",0,0}

desired output however should be a variable length array without the empty/zero elements :

for 1st row >> {"M1-item1"}
for 2nd row >> {"M2-item1","M2-item2","M2-item3","M2-item4"}

CodePudding user response：

Given that the original question has changed, this is a much simpler problem. Let's say that we have some named ranges:

This would be the formula to do the job:

=INDEX(
    FullRange,
    XMATCH(DropdownSelection,MenuList,0),
    XMATCH(
        SEQUENCE(
            ,
            MAX(
                MMULT(
                    SEQUENCE(,ROWS(FullRange),1,0),
                    MMULT((1-ISBLANK(FullRange))*(DropdownSelection=MenuList),
                    (SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1)
                )
            )
        ),
        MMULT(
            SEQUENCE(,ROWS(FullRange),1,0),
            MMULT(
                (1-ISBLANK(FullRange))*(DropdownSelection=MenuList),
                (SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1
            )
        ),
        0
    )
)

Sorry it's so ugly in the middle. It uses ISBLANK to find which cells are used.

In particular, this part:

SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1

produces an upper triangular matrix.

If we have a matrix that contains this:

(1-ISBLANK(FullRange))*(DropdownSelection=MenuList)

it will contain only those cells we actually want.

By taking the matrix product, we get a matrix with 0's everywhere except on the row we're after. On the row we're after, it has 1 on the first cell we want up to the 2nd cell, 2 on the second cell we want up to the third, and so on. By XMATCHing from the left (going right), it will only match on those cells we want.

CodePudding user response：

This is a classic, painful problem. I like to solve it by firstly moving all the elements in the range into a single column, like this:

=LET(
     InputArray,
     B1:G8,
     InputRowCount,
     ROWS(InputArray),
     TotalCellCount,
     COLUMNS(InputArray)*InputRowCount,
     LookupRow,
     MOD(SEQUENCE(TotalCellCount,,0),InputRowCount),
     LookupColumn,
     (SEQUENCE(TotalCellCount,,0)-LookupRow)/InputRowCount,
     RearrangedAsColumn,
     INDEX(InputArray,LookupRow,LookupColumn),
     RearrangedAsColumn_IsBlank,
     INDEX(ISBLANK(InputArray),LookupRow,LookupColumn),
     Result,
     FILTER(RearrangedAsColumn,1-RearrangedAsColumn_IsBlank),
     Result
)

I used a LET function here, because it allows me to define variables, and makes the explanation a little clearer.

Stepping through:

I hope this is what you're after!