How to apply following formula for visible cells only?

Question

=SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,A2:J6)&"</s></t>","//s")))

The above formula is for combining multi columns in one column.

I need to apply above formula for visible cells only. "d" is filtered in "A" column but still present in the results.

Please help me.

Thank you.

MyFile

Answer 1

I found a workaround that may help you if the dataset isn't too large:

This solution requires a helper column.

If we put =ROW()^10 in K2 and drag that down to the end of your range (or use =ROW(A2:J6)^10 at once) then we created a helper column of values that if we sum combinations they will always sum up to unique values. We will use that in our solution.

As we manually filter out any of the rows, the lines of the helper column would get filtered out as well. The function SUBTOTAL has the built in function to work with unfiltered data only. In this case we use SUM and the argument for that is 109. We can make use of that in our solution:

=LET(range,   A2:J6,
     helper,  K2:K6,
     r,       ROWS(range),
     visible, REDUCE("",BASE(SEQUENCE(SUM(COMBIN(r,SEQUENCE(r)))),2,r),
              LAMBDA(x,y,
              IF( SUM(FILTER(helper,--MID(y,SEQUENCE(r),1)))=SUBTOTAL(109,helper),
                  FILTER(range,--MID(y,SEQUENCE(r),1)),x))),
SORT(UNIQUE(TOCOL(visible,1))))

before manual filter

after applying manual filter Basically the formula checks if the sum for all possible combinations of the helper column equals the filtered subtotal of the helper column than apply a filter to the combination of these rows.

I know REDUCE or LAMBDA don't work in Office 2021, so I changed the FILTERXML to TOCOL in this formula.

While it will not work in Office 2021 I think you do have access to excel for the web and all this will work there. I hope that helps you in your situation.

---

EDIT Excel 2021 solution:

Alternate solution which I think uses solely functions compatible with Excel 2021:

=LET(range,   Sheet1!A2:J6,
     helper,  Sheet1!K2:K6,
     r,       ROWS(range),
     combin,  SUBSTITUTE(MID(BASE(SEQUENCE(SUM(COMBIN(r,SEQUENCE(r)))),2,r),SEQUENCE(,r),1)*SEQUENCE(,r),0,""),
     rowcomb, --IF(MMULT(IFERROR(INDEX(helper,combin,0),0),SEQUENCE(r,,,0))=SUBTOTAL(109,helper),combin,""),
     res,     TRANSPOSE(FILTER(rowcomb,ISNUMBER(INDEX(rowcomb,,1)))),
     resrows, INDEX(range,FILTER(res,ISNUMBER(res)),SEQUENCE(,COLUMNS(range))),
SORT(UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",1,resrows)&"</s></t>","//s"))))