I am looking to make a pre-existing function more dynamic such that the user can readily change the filter conditions without needing to delve into the formula and changing manually. I would prefer a solution that doesn't require VBA to make it easier to understand for non-VBA users - but if all else fails then I am happy to explore VBA.
Some context There are two parts to this problem, part 1 - filtering and part 2 - results output. Firstly though, brief context. I have been given a spreadsheet which another team member has generated that is used to group items from a larger range based on the values in two columns. The larger range has a lot of info that isn't relevant so I have distilled down into an example with just the three columns of interest - please note that I will need to be able to dynamically refer to the length of the range as items may be added or removed.
Example large range to be filtered Table 1
| Column A | Column B | Column C |
|---|---|---|
| type 1 | High | High |
| type 2 | High | Med |
| type 3 | Med | High |
| type 4 | Low | Med |
etc for more rows.
Column A in the above table is the value of interest I would like to return, and Column B/C are a pair of conditions which can be either "High", "Med", or "Low".
part 1 - filtering
I am looking to dynamically filter the large range shown in the example above to create 4 individual groups of the values in column A, each of the 4 groups are based on a set of condition pairs such as Group 1 containing values for (High * High) + (High * Med) + (Med * High).
The user can input the condition pairs they want in another table which looks like this (these are the actual values I want to filter with): Table 2
| Column A | Column B |
|---|---|
| Group 1 | High, High or High, Med or Med, High |
| Group 2 | Low, High or Low, Med |
| Group 3 | High, Low or Med, Low or Med, Med |
| Group 4 | Low, Low |
The person who authored the spreadsheet has the formula setup like this:
=IFERROR(CHOOSECOLS(FILTER('sheet'!$B$2:$AC$34,(('sheet'!$E$2:$E$34="High")*('sheet'!$F$2:$F$34="High"))+(('sheet'!$E$2:$E$34="Med")*('sheet'!$F$2:$F$34="High"))+(('sheet'!$E$2:$E$34="High")*('sheet'!$E$2:$E$34="Med"))),2),"Nothing Found")
This works fine but if the end user ever wants to change the pairs in table 2 it becomes a faff. What I am then looking for is a way to adjust the above formula to dynamically incorporate the written logic within table 2. I have been down the route of trying a few things but cant quite put the pieces together. I have generated an array with the condition pairs using this,
=TRANSPOSE(VSTACK(TEXTBEFORE(TEXTSPLIT(U18," or "),","),TEXTAFTER(TEXTSPLIT(U18," or "),", ")))
and couldn't quite get the original formula working to reference the condition pairs array dynamically. Then I tried getting direct logical output from the original range by using this - duplicated so I ended up with 3x arrays with 2 columns each, changing the High value to be Med and Low .
=DROP(MAKEARRAY(ROWS('sheet'!$E$2:$F$34),COLUMNS('sheet'!$E$2:$F$34),LAMBDA(r,c,INDEX('sheet'!$E$2:$F$34,r,c)="High")),2)+0
(note - I have a DROP() in here because there are two heading rows)
Part 2 - results output This is the bit where I am pretty sure I will have to resort to VBA but wanted to ask anyway. The 4 grouped results from the previous step are currently displayed in a 2x2 grid where each square is actually comprised of a wrapped list from each of the groups. I'm not totally sure how to represent the grid visually in this post and I am unable to share the workbook, so hopefully this explanation will do.
Essentially, each grid square in the 2x2 represents the list of items from a group, outlined in table 2. The squares need to be equally sized even if there are no items within them so that the end 2x2 grid is a symmetrical square.
Currently this is done manually (ish) by using:
| Grid Title 1 | Grid Title 2 | |
|---|---|---|
| Grid Title 3 | Group 1 | Group 2 |
| Grid Title 4 | Group 3 | Group 4 |
with the formulas for each group referring the filtered array from step 1 with this formula:
=IFERROR(WRAPCOLS(F4#,6),"")
Note that the headings are centred across selection currently so even with the final display having more items in the grid, the heading remains across the top for example.
Currently the 2x2 grid has to be altered manually when more items are added to the original larger range to move the formulas around. I have experimented with HSTACK() and VSTACK() using an IFERROR(,"") wrapper to fill in the blanks but I couldn't quite get it to keep the symmetrical shape.
Any hep/ guidance anyone can provide would be much appreciated.
Edit - Big thanks to user P.b for help with the first step. The output now gives a 2x2 grid of comma separated values as expected. The issue now is how that 2x2 grid can be extended so that each grid square is a square block of cells, keeping the 2x2 shape - essentially to dynamically tessellate individual cells for each of the comma separated values currently output from the answer below. For clarity the current output looks something like this:
| Grid Title 1 | Grid Title 2 | |
|---|---|---|
| Grid Title 3 | type1,type2,type7,type9 | type3,type6 |
| Grid Title 4 | type5 | type4,type8 |
Whereas what I was hoping to achieve looks something like this:
| Grid Title 1 | Grid Title 2 | |||||
|---|---|---|---|---|---|---|
| Grid Title 3 | type1 | type2 | type7 | type3 | type6 | |
| type9 | ||||||
| Grid Title 4 | type5 | type4 | type8 | |||
So it would be fixed at three columns for each grid square and then the number of rows dynamically adjusts based on the maximum number of conditions that are included.
Not the end of the world if it has to be two formulas, so one as the answer below states of =WRAPROWS(MAP(B2:B5,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!A2:A5,ISNUMBER(XMATCH(sheet!B2:B5&sheet!C2:C5,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"")))),2)
then another for the main grid generation
Edit 2 - I have fixed the above edit points using a combination of HSTACK () and VSTACK(). This is very convoluted but have included in case it is of use to anyone in the future.
=IFERROR( VSTACK( VSTACK( HSTACK( HSTACK("","Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))), HSTACK("Not Urgent",MAKEARRAY(1,2,LAMBDA(x,y,"")))), HSTACK( VSTACK("Important",MAKEARRAY(MAX(MAP(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),LAMBDA(z,MAX(ROUNDUP(ROWS(TEXTSPLIT(z,,","))/3,0)))))-1,1,LAMBDA(x,y,""))), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),1,1),,","),3)),1), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),1,2),,","),3)),1)) ), HSTACK( VSTACK("Not Important",MAKEARRAY(MAX(MAP(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),LAMBDA(z,MAX(ROUNDUP(ROWS(TEXTSPLIT(z,,","))/3,0)))))-1,1,LAMBDA(x,y,""))), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),2,1),,","),3)),1), DROP(VSTACK(HSTACK("Urgent",MAKEARRAY(1,2,LAMBDA(x,y,""))),WRAPROWS(TEXTSPLIT(INDEX(WRAPROWS(MAP(AD16:AD19,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!$C$2:$C$34,ISNUMBER(XMATCH(sheet!$E$2:$E$34&sheet!$F$2:$F$34,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"Blank")))),2),2,2),,","),3)),1) ) ), "")
If I understood it correctly you're trying to filter sheet!A2:A5 types based on their values in column B and C matching the concatenated group conditions in the other sheet column B2:B5
=WRAPROWS(MAP(B2:B5,LAMBDA(m,ARRAYTOTEXT(FILTER(sheet!A2:A5,ISNUMBER(XMATCH(sheet!B2:B5&sheet!C2:C5,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"")))),2)
This can be done by mapping the group condition cells and performing a filter on the textsplitted conditions per mapped cell and wrapping the outcome in ARRAYTOTEXT. This way your input and output equals the number of groups. Since this outputs 4 cells below eachother, we wrap it in WRAPROWS, so the outcome is 2x2 as required.
If you can't dynamic referencing, look into using sheet!A:.A (trimrange referencing). You could also use something like sheet!A2:.A999
Not sure why you want to spill the cells in unpredictable sizes, but you could accomplish that using:
=LET(a,MAP(B2:B5,LAMBDA(m,TEXTJOIN({",",",","|"},,FILTER(sheet!A2:A10,ISNUMBER(XMATCH(sheet!B2:B10&sheet!C2:C10,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"")))),
b,MAX(MAP(a,LAMBDA(x,ROWS(TEXTSPLIT(x,,"|"))))),
L,LAMBDA(x,IFNA(EXPAND(TEXTSPLIT(INDEX(+a,x,1),",","|"),b,3),"")),
VSTACK(HSTACK(L(1),L(2)),
HSTACK(L(3),L(4))))
Where a is an array the joined types per group. The values are delimited by , but each 3rd value by | (for later use in Textsplit).
b counts the max number of rows if each value of a would get splitted by | as row delimiter.
Next L is created; a function to split the xth (input value for the function) row of a by column delimiter , and row delimiter | and expand it to the b number of rows and 3 columns.
Since any empty values in the spill range of the Textsplit result and in the expand result will produce #N/A-error, we wrap it in IFNA to convert these to blanks all at once.
If the expanding is purely for aesthetical reasons, you may wat to consider to spill it in 4 cells and making use of a line end character in combination with wraptext:
=WRAPROWS(MAP(B2:B5,LAMBDA(m,TEXTJOIN({",",",",","},,FILTER(sheet!A2:A10,ISNUMBER(XMATCH(sheet!B2:B10&sheet!C2:C10,SUBSTITUTE(TEXTSPLIT(m,," or "),", ",))),"")))),2)
"," being a comma followed by alt+enter
Link to workbook containing solution: Link