How to choose the program(s) with closest date from today?

[Mert]

Hi. My data is about a group of workshop programs to be delivered during 2024.

Data has 3 regions: America, Asia and Europe.

There are 4 different workshop programs: A, B, C and D. 

Each program is delivered over 3, 4 or 5 days, and is repeated multiple times during the year for convenience.

For Example:

Region	Program	Session Date	Program Start Date	Program End Date
AMERICA	B	05/08/2024	05/08/2024	15/08/2024
AMERICA	B	08/08/2024	05/08/2024	15/08/2024
AMERICA	B	12/08/2024	05/08/2024	15/08/2024

In the table above, program B has 3 sessions delivered in 3 dates which are close to each other.

I would like to create a table like below:

	A	B	C	D
AMERICA
ASIA
EUROPE

In empty cells, I would like to put dates from Program Start Date field and it should be the start date of the closest program from today.

I attach sample data with an additional field Program Start Date From Today. I will be thankful if anyone could help me with the workflow. TIA!  

[DataNath · Accepted solution]

Hey @Mert, how does this look? After an ascending sort on the time differential from today, we can Cross-Tab the results. We group by [Region], using the [Program] as the new field headers and then for the aggregation just take the first value i.e. the lowest in the sort:

[CoG]

Will your data always only have upcoming events? If so, I agree with @DataNath 's simple and elegant solution. Otherwise, if historical events are also present, you will want to filter them out before the Sort and Cross Tab Tools.

[DataNath]

Good catch @CoG - hadn't considered negative time differentials!

[Mert]

@DataNath - Thank you very much for your quick help with a spot on solution 😊

@CoG - Thank you for the heads-up for the negative dates. I had shared the part of the workflow where I got stuck, only. In the wider workflow, exactly like your warning, I first filter out the sessions which were already delivered.

Have a nice Sunday both 🙌