Extracting next soonest date from sub-selection of data

[Saraphas]

Hi Everyone, 

I have tabular data, where each row refers to a particular milestone for a given project, and I am trying to create a column that is replicated on all the rows which indicates what the next milestone is, and what type of milestone that is.

The milestones are consistent across all projects, so what I would like to do use today's date as a reference, search for the next soonest date and then probably use a join to look for Project ID + that date to find the milestone 

Below is an example output - the first three columns are inputs, and the final two are the onest that I'd like to end up with. I have been playing around with Cross tabbing the data, but to no avail. 

Would be super grateful for any input available! I'm not sure whether this is something that's very simple, that I can't figure out, or if it's complex... 

Many Thanks!

Sara, 

Project	Milestone	Date	Next Milestone Type	Next Milestone Date
1	Prep	28/09/2018	Completion	06/11/2020
1	Staging	16/01/2019	Completion	06/11/2020
1	Pre-prep	06/05/2019	Completion	06/11/2020
1	Run	24/08/2019	Completion	06/11/2020
1	Launch	12/12/2019	Completion	06/11/2020
1	Process	31/03/2020	Completion	06/11/2020
1	Checking in	19/07/2020	Completion	06/11/2020
1	Completion	06/11/2020	Completion	06/11/2020
1	Sign off	24/02/2021	Completion	06/11/2020
1	Close	14/06/2021	Completion	06/11/2020
2	Prep	28/11/2018	Run	24/10/2020
2	Staging	18/03/2019	Run	24/10/2020
2	Pre-prep	06/07/2019	Run	24/10/2020
2	Run	24/10/2019	Run	24/10/2020
2	Launch	11/02/2020	Run	24/10/2020
2	Process	31/05/2020	Run	24/10/2020
2	Checking in	18/09/2020	Run	24/10/2020
2	Completion	06/01/2021	Run	24/10/2020
2	Sign off	26/04/2021	Run	24/10/2020
2	Close	14/08/2021	Run	24/10/2020
3	Prep	28/07/2018	Sign Off	24/12/2020
3	Staging	15/11/2018	Sign Off	24/12/2020
3	Pre-prep	05/03/2019	Sign Off	24/12/2020
3	Run	23/06/2019	Sign Off	24/12/2020
3	Launch	11/10/2019	Sign Off	24/12/2020
3	Process	29/01/2020	Sign Off	24/12/2020
3	Checking in	18/05/2020	Sign Off	24/12/2020
3	Completion	05/09/2020	Sign Off	24/12/2020
3	Sign off	24/12/2020	Sign Off	24/12/2020
3	Close	13/04/2021	Sign Off	24/12/2020

[Greg_Murray]

Here you go! 

[Saraphas]

Thank you this works perfectly!