Create list based on multiple criteria
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!
- 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)) )
