Sorting by highest number by unique group ID

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!

sample.xlsx

Workflow

Datasets

Regex

Tips and Tricks

Parse

Common Use Cases

Best Practices

Help

Preparation

Expression

Accepted answers

DataNath

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

Comments and Deployment based on max Version number.yxmd

binu_acs

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

Version_1103.yxmd

All comments

DataNath

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

Comments and Deployment based on max Version number.yxmd

binu_acs

@csodesza One way of doing this

Version_1103.yxmd

csodesza

@binu_acs 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!

binu_acs

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

Version_1103.yxmd

csodesza

@binu_acs @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! 😁

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703