Excel dynamic filter array

Question

Hello my dear excel friends!

My "List" table consists of 10 columns that I filter by needed criterias in the second tab, which works perfectly. Now I have problems with the following filtering:

Cell B7 = Name

Cell B8:F8 = Items 1-5

Cell B9:F8 should be the order number (different amount for each item) of the respected items in the cells above.

Depending on the Name the items are spilling differently without any problems with

=IFERROR(TRANSPOSE(UNIQUE(FILTER(List[Item];(List[Name]=$B$7))));"")

Now I want to spill out the order numbers for each item in each column. The amount of orders can differ depending on the column. Unfortunately for now I can do it only for one column and have to copy the function to the next column:

=IFERROR(UNIQUE(FILTER(List[Order];(List[Item]=B8)*(List[Name]=$B$7)));"")

I have tried to do it with IFS but could not achieve the needed results. I can imagine something like B9# but somehow can not implement it into my function. Is there a way to do it?

Jos Woolley · Accepted Answer · 2022-02-14 13:15:32Z

1

Very difficult to give a rigorous solution for such a case. The following is convoluted though should work for smallish ranges:

=LET(μ,List[Item],κ,List[Name],η,B7:B11,ρ,MAX(COUNTIF(κ,η)),λ,TRIM(MID(FILTERXML("<a><b>"&TEXTJOIN(REPT(CHAR(32),50),,IFERROR(IF(INDEX(TRANSPOSE(κ),SEQUENCE(,ROWS(κ)+1))=η,TRANSPOSE(μ),""),"</b><b>"))&"</b></a>","//b"),SEQUENCE(,ρ,1,50),50)),IFERROR(INDEX(λ,SEQUENCE(ROWS(η)),SEQUENCE(,ρ)),""))

I've assumed that B7:B11 is your list of names. Amend as required.

In my opinion a non-spill formula to be copied down is preferable.

edited Feb 14, 2022 at 13:15

answered Feb 14, 2022 at 13:09

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Niksonik Over a year ago

Thank you for your solution! I have just recently started to work with LET and LAMBDA and like them a lot. I can imagine this approach for more bigger tables, but for only few items, like in this case, it is easier to go with a "copy-paste" solution. I have already inserted my last function to all columns in the needed range. It is not really dynamic, but seems to be working :)

Collectives™ on Stack Overflow

Excel dynamic filter array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related