CODEWITHSUNDEEP
excelfilterdynamic-arrays
Niksonik
Niksonik

Reputation: 1

Excel dynamic filter array

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?

Upvotes: 0

Views: 629

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9052

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.

Upvotes: 1

Related Questions