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 ScopeDateStatusA1234ABOut Of Scope5/19/2023ClosedB1234BAOut Of Scope5/19/2023OpenC1234CAOut Of Scope5/19/2023OpenD1234DAIn scope5/14/2023OpenA1234ABIn scope5/16/2023OpenA1234ABIn scope5/15/2023OpenA1234ABIn scope5/14/2023OpenD1234DBOut Of Scope5/19/2023Closed 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 ?

Sorting Data

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 ?

Data Investigation

Accepted answers

Raj

Hope this help

Sort sol community.yxmd

All comments

binu_acs

@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

Hope this help

Sort sol community.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703