Feb 09 2022 03:38 AM
Good morning all! I'm trying to modify my current formula to filter results by a column value if it matches another table's column. My example dataset is attached, but here's the goal:
Table1
Names | Zones | 1/1/2022 | 4/1/2022 | 7/1/2022 | 10/1/2022 | 1/1/2023 | 4/1/2023 | 7/1/2023 |
Name 1 | NS | 18.5 | 15 | 15 | 45 | 45 | 52 | 52 |
Name 1 | AZ | 18.5 | 15 | 15 | 45 | 45 | 52 | 52 |
Name 1 | PPV | 18.5 | 15 | 15 | 45 | 45 | 52 | 52 |
Name 2 | NS | 18.5 | 15 | 18.5 | 33.5 | 33.5 | 40.5 | 40.5 |
Name 2 | SS | 18.5 | 15 | 18.5 | 33.5 | 33.5 | 40.5 | 40.5 |
Name 2 | AZ | 18.5 | 15 | 18.5 | 33.5 | 33.5 | 40.5 | 40.5 |
Name 3 | SS | 21.5 | 21.5 | 21.5 | 34 | 35.5 | 35.5 | 35.5 |
Name 3 | AZ | 21.5 | 21.5 | 21.5 | 34 | 35.5 | 35.5 | 35.5 |
Name 4 | NS | 17.5 | 17.5 | 17.5 | 45 | 50.5 | 50.5 | 50.5 |
Name 4 | AZ | 17.5 | 17.5 | 17.5 | 45 | 50.5 | 50.5 | 50.5 |
Name 5 | AZ | 19 | 19 | 19 | 34 | 35.5 | 35.5 | 35.5 |
Name 6 | NS | 0 | 0 | 6 | 36 | 41.5 | 41.5 | 41.5 |
Name 6 | AZ | 0 | 0 | 6 | 36 | 41.5 | 41.5 | 41.5 |
Name 7 | NS | 0 | 0 | 2.5 | 32.5 | 32.5 | 32.5 | 32.5 |
Name 7 | AZ | 0 | 0 | 2.5 | 32.5 | 32.5 | 32.5 | 32.5 |
Name 7 | PPV | 0 | 0 | 2.5 | 32.5 | 32.5 | 32.5 | 32.5 |
Name 8 | NS | 0 | 0 | 0 | 15 | 20.5 | 20.5 | 20.5 |
Name 8 | SS | 0 | 0 | 0 | 15 | 20.5 | 20.5 | 20.5 |
Table 2
Project | Zone | Start | Complete | Sum | Assigned |
Project 1 | NS | 10/1/2020 | 9/1/2023 | 15 | Name 1 |
Project 1 | NS | 10/1/2020 | 9/1/2023 | 15 | Name 2 |
Project 1 | NS | 10/1/2020 | 9/1/2023 | 15 | Name 3 |
Project 1 | NS | 10/1/2020 | 9/1/2023 | 15 | Name 4 |
Project 1 | NS | 10/1/2020 | 9/1/2023 | 15 | Name 5 |
Project 2 | SS | 4/1/2021 | 3/1/2022 | 3.5 | Name 1 |
Project 2 | SS | 4/1/2021 | 3/1/2022 | 3.5 | Name 2 |
Project 3 | SS | 10/1/2021 | 9/1/2022 | 2.5 | Name 3 |
Project 3 | SS | 10/1/2021 | 9/1/2022 | 2.5 | Name 4 |
Project 4 | SS | 11/23/2021 | 12/1/2022 | 4 | Name 3 |
Project 4 | SS | 11/23/2021 | 12/1/2022 | 4 | Name 5 |
Project 5 | NS | 7/1/2022 | 12/1/2023 | 2.5 | Name 6 |
Project 5 | NS | 7/1/2022 | 12/1/2023 | 2.5 | Name 7 |
Project 6 | NS | 7/1/2022 | 12/1/2023 | 3.5 | Name 6 |
Project 6 | NS | 7/1/2022 | 12/1/2023 | 3.5 | Name 2 |
Project 7 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 1 |
Project 7 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 4 |
Project 7 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 7 |
Project 7 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 8 |
Project 7 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 6 |
Project 7 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 5 |
Project 8 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 1 |
Project 8 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 4 |
Project 8 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 2 |
Project 8 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 6 |
Project 8 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 7 |
Project 8 | NS | 9/1/2022 | 7/1/2026 | 15 | Name 3 |
Project 9 | SS | 12/23/2022 | 9/28/2023 | 5.5 | Name 3 |
Project 9 | SS | 12/23/2022 | 9/28/2023 | 5.5 | Name 5 |
Project 9 | SS | 12/23/2022 | 9/28/2023 | 5.5 | Name 8 |
Project 9 | SS | 12/23/2022 | 9/28/2023 | 5.5 | Name 6 |
Project 9 | SS | 12/23/2022 | 9/28/2023 | 5.5 | Name 4 |
Project 10 | SS | 2/21/2023 | 6/20/2024 | 7 | Name 1 |
Project 10 | SS | 2/21/2023 | 6/20/2024 | 7 | Name 2 |
My current formula is:
=IFERROR(SUM(
MMULT(
--(Table2[Assigned] = Table1[@[Name]:[Name]] ),
SEQUENCE( COLUMNS( Table2[Assigned] ), , 1,0) ) *
Table2[[Sum]:[Sum]] *
( Table2[[Start]:[Start]] <=
DATEVALUE(Table1[[#Headers],[1/1/2022]] )
) *
( Table2[[Complete]:[Complete]] >=
DATEVALUE(Table1[[#Headers],[1/1/2022]] )
)
),"")
But I need it to say if Table 1's zone matches the zone in Table 2 for that person, then return value from Sum column, that way I get the value for the person for the respective zone.
Thank you!
Feb 09 2022 05:22 AM
@keltzjd I would recommend that you use the FILTER in stead of the a rather complicated combination of MMULT and SEQUENCE. The basic syntax is as follows:
=SUM(FILTER(Table2, (Assigned=Name)*(Zone=Zones)*(Start<=HeaderDate)*(Complete>=HeaderDate))
Since you already know how to make the structured references absolute and how to overcome the fact that the "HeaderDate" in Table1 is a text, I trust you can get it to work yourself.
But, I couldn't resist to create something in PowerQuery as well. See attached.
Feb 09 2022 06:39 AM
Feb 09 2022 07:06 AM
@keltzjd I guess you can expand it with with one more rule. I.e. multiply by an array where the Zone in table2 agrees to the zone in table1. Similar to what you have now for finding matching names.
Feb 09 2022 07:20 AM
Feb 09 2022 09:25 AM
Solution@keltzjd I used the file you sent earlier. Seems to work as expected. See Attached.
Feb 09 2022 09:25 AM
Solution@keltzjd I used the file you sent earlier. Seems to work as expected. See Attached.