Multirow formula to identify the wave

[Cherub7210]

How would i use the multirow formula to identify wave 1, 2, 3 using the due on date? Below is the example

				Output: Wave identifier
106751	Action item 2	Deployment	10/31/2024	Wave 2
106751	Action item 1	Deployment	9/29/2023	Wave 1
114400	Team 1	Deployment	10/2/2024	Wave 1
114400	Team 2	Deployment	12/2/2024	Wave 2

If there would be another tool i can use to do this open to suggestions

[binuacs]

@Cherub7210 what is the expected output?

[Cherub7210]

@binuacs the expected out is in the expected output column

Proj Id	Project Name	Task	Due on Date	Expected Output
106751	Action item 2	Deployment	10/31/2024	Wave 2
106751	Action item 1	Deployment	9/29/2023	Wave 1
114400	Team 1	Deployment	10/2/2024	Wave 1
114400	Team 2	Deployment	12/2/2024	Wave 2

[binuacs]

@Cherub7210 can you explain the logic?

[Cherub7210 · Accepted solution]

@binuacs using the due on date i would like to identify what wave would it be. So it can be Wave 1 to Wave N depending on how may project id that are listed. apologies i think my example is incorrect. Here is an updated example. Wave 1 would be the earliest date, 2 the would be the next date to N where N is the newest date.

In the example, proj id 106751 has 2 instances and due on date 9/29/23 is the earliest and is tagged as wave 1 and then 10/31/24 would be Wave 2 

Proj  Id	Project Name	Task	Due on Date	Expected Output
106751	Action item 1	Deployment	10/31/2024	Wave 2
106751	Action item 1	Deployment	9/29/2023	Wave 1
114400	Team 1	Deployment	10/2/2024	Wave 1
114400	Team 1	Deployment	12/2/2024	Wave 2

hope this gives a clearer picture 

[binuacs · Accepted solution]

@Cherub7210 one way of doing this