Forum Discussion
Create list based on multiple criteria
- Assuming you have Excel 365 I think the easiest solution would be 4 formulas like this:
c4 = WRAPCOLS( FILTER(R:R, (T:T="High")*(U:U="High"), ""), 16, "")
then J4, C20, and J20 would have the same formula except replacing "High" with "Low" accordingly
Now this assume the quadrants will have enough spots to accomodate the number of tasks found. Alternatively you could get 'fancy' and build the whole table at 1 time to guarantee it will fit all the data. Basically in C2 I would have a LET statement with HH, HL, LH, and LL defined as the formulas above and then VSTACK and HSTACK accordingly. You might consider if it makes more sense to fix the number of rows or number of columns. but something like:
=LET(
HH, WRAPCOLS( FILTER(R:R, (T:T="High")*(U:U="High"), ""), 16, ""),
HL, WRAPCOLS( FILTER(R:R, (T:T="High")*(U:U="Low"), ""), 16, ""),
LH, WRAPCOLS( FILTER(R:R, (T:T="Low")*(U:U="High"), ""), 16, ""),
LL, WRAPCOLS( FILTER(R:R, (T:T="Low")*(U:U="Low"), ""), 16, ""),
HSTACK(VSTACK(HH,LH), VSTACK(HL,LL)) )
c4 = WRAPCOLS( FILTER(R:R, (T:T="High")*(U:U="High"), ""), 16, "")
then J4, C20, and J20 would have the same formula except replacing "High" with "Low" accordingly
Now this assume the quadrants will have enough spots to accomodate the number of tasks found. Alternatively you could get 'fancy' and build the whole table at 1 time to guarantee it will fit all the data. Basically in C2 I would have a LET statement with HH, HL, LH, and LL defined as the formulas above and then VSTACK and HSTACK accordingly. You might consider if it makes more sense to fix the number of rows or number of columns. but something like:
=LET(
HH, WRAPCOLS( FILTER(R:R, (T:T="High")*(U:U="High"), ""), 16, ""),
HL, WRAPCOLS( FILTER(R:R, (T:T="High")*(U:U="Low"), ""), 16, ""),
LH, WRAPCOLS( FILTER(R:R, (T:T="Low")*(U:U="High"), ""), 16, ""),
LL, WRAPCOLS( FILTER(R:R, (T:T="Low")*(U:U="Low"), ""), 16, ""),
HSTACK(VSTACK(HH,LH), VSTACK(HL,LL)) )
I just tried it and IT WORKS!!!!! Thank you very much for replying and giving me my sanity back lol.. I spent hours trying to figure this out..
Alternatively, if I only had one criteria, how would the formula change?
- not sure I understand. maybe you are thinking this which will wrap the results from col R if columnT="High":
c4 = WRAPCOLS( FILTER(R:R, (T:T="High"), ""), 16, "")Thank you for being so helpful. I wish I had your Excel skill level! 🙂
I have one more ask: I changed the matrix a bit and will require a list based on two criteria, but a little different than the scenario above. One criteria will be the priority level (Low, Medium, High, Critical) and the other will be product type (Auto, Farm) where I would like it to add text in addition to the text written in the table.
Let's say I enter "Create fleet presentation", and the criteria I choose is "High", and then "Auto". I would like it to appear in the matrix as, "Create fleet presentation - Auto", in the High Priority box. I hope that makes sense! If not, I will certainly clarify for you.
Thank you in advance. 🙂
