Sorting by highest number by unique group ID

[csodesza]

Hello,

I have a workflow where I parsed the numbers from the 'ID' column to the 'Version_EDIT' column. I am trying to create a formula where I can sort by the highest version in 'Version_EDIT' by unique 'ID' group. Anything less than the highest version, the number '0' should be applied in the 'Deployment' column and in the 'Comments' column should write 'lesser version'.

In the highest version rows, leave the 'Comments' and 'Deployment' columns blank (as is).

Base file(example):

ID	VERSION	Deployment	Comments	Version_EDIT
5698-A14	V3			3
5698-A14	V2			2
5698-A14	V1			1

Expected Output(example): 

ID	VERSION	Deployment	Comments	Version_EDIT
5698-A14	V3			3
5698-A14	V2	0	lesser version	2
5698-A14	V1	0	lesser version	1

Please see attached of my working file. Thank you!

[DataNath · Accepted solution]

Hey @csodesza, how does this look?

I've:

1 - Parsed the version number

2 - Sorted on this

3 - Found the max for each ID and re-appended this

4 - Done a couple of checks to see if the version # = the max for that ID

5 - If not, apply the Comments/Deployment requirements

6 - If so, left them blank

7 - Removed temporary 'Max' field used for logic

[binuacs]

@csodesza One way of doing this

[csodesza]

@binuacs What about in the scenario when we have the same unique ID with the same version? 

For example, I'm receiving this output when implementing with your rules: 

ID	Version	Deployment	Comments
5698-A14	V3
5698-A14	V3	0	Lesser Version
5698-A14	V3	0	Lesser Version

Expected Output:

ID	Version	Deployment	Comments
5698-A14	V3
5698-A14	V3
5698-A14	V3

Essentially they should all be null values since it can't recognize a higher version with that unique ID.

Much appreciative of your help as always!

[binuacs · Accepted solution]

@csodesza updated the workflow and attached. can you try now?

[csodesza]

@binuacs @DataNath Thank you both very much! Both of your solutions worked and I now have two ways to approach this type of problem. Appreciate all the help! 😁