Sorting Data

[AbhijeetChib]

I have the following data, which needs to be sorted according to most recent date. However if the most recent date has a group which is "Out of Scope" then that needs to be changed to the group of the next most recent date.  For example

Ticket Number	Group	Scope	Date	Status
A1234	AB	Out Of Scope	5/19/2023	Closed
B1234	BA	Out Of Scope	5/19/2023	Open
C1234	CA	Out Of Scope	5/19/2023	Open
D1234	DA	In scope	5/14/2023	Open
A1234	AB	In scope	5/16/2023	Open
A1234	AB	In scope	5/15/2023	Open
A1234	AB	In scope	5/14/2023	Open
D1234	DB	Out Of Scope	5/19/2023	Closed

In this data ticket number A1234 needs to sorted and the most recent date and status has to be picked. In this case A1234, date 5/19/2023 and status closed. However the field group is AB which is out of scope, in this case it needs to be changed to the groupof the next most recent date which is A1234-5/16/2023 and status open. 

Any ideas ?

[binuacs]

@AbhijeetChib @Would you be able to provide the expected result as well, that would help to understand the logic in better way 

[AbhijeetChib]

Input 

Ticket Number	Group	Scope	Date	Status
A1234	AB	Out Of Scope	5/19/2023	Closed
B1234	BA	Out Of Scope	5/19/2023	Open
C1234	CA	Out Of Scope	5/19/2023	Open
D1234	DA	In scope	5/14/2023	Open
A1234	CD	In scope	5/16/2023	Open
A1234	CD	In scope	5/15/2023	Open
A1234	CD	In scope	5/14/2023	Open
D1234	DB	Out Of Scope	5/19/2023	Closed

Here is the expected Output

Ticket Number	Group	Scope	Date	Status
A1234	CD	Out Of Scope	5/19/2023	Open
B1234	BA	Out Of Scope	5/19/2023	Open
C1234	CA	Out Of Scope	5/19/2023	Open
D1234	DA	Out of Scope	5/19/2023	Open

[Raj · Accepted solution]

Hope this help

[korevis]

Hi Abhijeet,

You need to take two steps to get the mentioned Result for all the ticker Number. 

Step 1 : add the sort tool, select the date column and order select the Descending.

Step 2 : add the Filter   [Scope] = "In scope" and [Status] = "Open"

Click the Run you get the result.