Create list based on multiple criteria

[Ocean818]

Hi everyone!

I am creating a task priority matrix in Excel and am stumped on the exact formula I need to accomplish what I am looking for.

Column R is where I enter daily tasks, and Columns T & U each have a dropdown menu with two options - "High" or "Low". My Matrix has 4 quadrants and I would like the tasks to auto-fill in the appropriate quadrant depending on the selections for Columns T & U (High/High; High/Low; Low/Low; Low/High). 

I need to make sure that if I enter a task in, for example, cell R18, it will carry over to the next available field in the appropriate quadrant, for example, cell C4.

My Table runs from rows 5 to 35, and the Matrix is a rectangle made up of 4 rectangles - 2 upper and 2 lower). Quadrant 1 = c4 to i4 / Rows 4-19; Quadrant 2 = J4 to P4 / Rows 4-19; Quadrant 3 = c20 to i20 / Rows 20 to 35; Quadrant 4 = J20 to P20 / Rows 20 to 35. 

Looking for any advice! Thank you!

[#2 · Accepted solution]

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

[#3]

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? 

[#4]

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

[#5]

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. :)

[#6]

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.

[#7]

Please see attached.. What you will see is that the data I enter under the table shows up in the matrix depending on priority status (First criteria). And the second criteria ("Auto" or "Farm" from dropdown list) shows up in brackets after main text.

Example: Go to grocery store (Farm).

@m_tarler

[#8]

So you could just 'form' that in the equation. assuming col R & U:
c4 = WRAPCOLS( FILTER(R:R & " ("&U:U&")", (T:T="High"), ""), 16, "")
it looks like you might be using a 'defined' table (on home tab -> Format as a Table) in which case you can use more informative references like:
c4 = WRAPCOLS( FILTER(Table1[Description] & " ("&Table1[Product Type]&")", (Table1[Priority]="High"), ""), 16, "")