Sorting/Removing dates to produce smallest DateTimeDiff

[gbufalino]

Hi, I am trying to get the smallest DayDiff between two dates, but I am having trouble with duplicates. Once the value that gives the lowest DayDiff has been used, it can't be used again. There cannot be date duplicates in either column (ER and Test). So, for each unique ER date, I need the corresponding unique Test Date that gives me the smallest DayDiff.

This is what my current output looks like, and I've bolded the values I want to keep:

Client Number	ER date	Test Date	DayDiff
1	11/6/2020	10/28/2020	9
1	6/18/2020	6/4/2020	14
1	3/29/2020	3/11/2020	18
1	11/15/2020	10/28/2020	18
1	8/17/2020	7/15/2020	33
1	12/2/2020	10/28/2020	35
1	8/17/2020	7/8/2020	40
1	12/9/2020	10/28/2020	42
1	11/6/2020	9/24/2020	43
1	11/15/2020	9/24/2020	52

Here's what it would look like if I had no duplicates and the correct pairing:

Client Number	ER date	Test Date	DayDiff
1	3/29/2020	3/11/2020	18
1	6/18/2020	6/4/2020	14
1	8/17/2020	7/15/2020	33
1	11/6/2020	10/28/2020	9
1	11/15/2020	NULL	NULL
1	12/2/2020	NULL	NULL
1	12/9/2020	NULL	NULL

The project focuses on clients who complete a Test before they go to the ER. 

Thanks for the help!

[mot · Accepted solution]

Hi @gbufalino,

You can use Summarize twie to reach the intended conclusion:

Workflow attached. I hope this helps. Best.

[gbufalino]

Worked great. Thanks!