0

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) ) ), "")

Improve this question
7
  • Is the combination High, Med equal to Med, High, or does the first value reflect the column B and the second the column C values of Tables? (Also, is Table1 an actual table, or a range?) Commented Mar 4 at 21:18
  • Next time try to focus on the problem you're facing. Is the trouble the 2x2 output, is it splitting the grouping effectively, or is it generating the joined result per group that you need help for? Commented Mar 4 at 21:36
  • 1
    @P.b - Thank you for your help with this. In answer to your first query the combinations are not interchangeable so first value is column B and second value is column C. The data which table1 reflects is stored as a range. For your second query, it is the splitting of groups I was facing challenges with and your solution has partially solved my issue - thank you! The final piece of the puzzle is extending the 2x2 output to have sub cells within it, keeping them square. I will adjust the wording in my question to clarify. Commented Mar 5 at 7:35
  • 1
    Thank you so much for your help! the adjustment made to your answer is exactly what I was looking for and a much neater solution than the second edit to my original post. Commented Mar 7 at 16:14
  • 1
    The =LET() formula from your answer is the one I have gone with in the end as the unpredictable cell sizes is something I am happy to live with (as the previous version of the workbook also included it and the team is now used to those challenges) Commented Mar 10 at 9:52

1 Answer 1

Reset to default
0

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

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.