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. 🙂- yes more explanation is needed. If you have quadrants (or in this case would that be octant as you have 4 levels (L,M,H,C) and 2 types (A, F) so that would be 2x4 or 4x2, but my point is that all the 'Auto' would be on the same row/column so why add that suffix "- Auto" but not add the priority level? Unless you really just want 4 columns of data sorted by priority level and then the suffix added so you know if it was Auto or Farm.
It would really help to have a small sample sheet with dummy data and how you want to see it.