Hi, I have below dataset. AccountSECURITYSTAGEFBES0001SEC9954S1F1S0002SEC000399S1F1S0002SEC000399S1F1S0002SEC000399S1F1S0002SEC000399S1F2S0002SEC000399S2F1S0003SEC12345S1F1S0003SEC12345S1F1S0003SEC12345S1F1S0004SEC4000S4F4S0004SEC4000S4F4S0005SEC5000S5F5S0005SEC5001S5F5S0005SEC5001S5F6 My Expected output is MasterId column in below output. AccountSECURITYSTAGEFBEMaserIdS0001SEC9954S1F1SEC9954_2S0002SEC000399S1F1SEC000399_2S0002SEC000399S1F1SEC000399_2S0002SEC000399S1F1SEC000399_2S0002SEC000399S1F2SEC000399_3S0002SEC000399S2F1SEC000399_4S0003SEC12345S1F1SEC12345_2S0003SEC12345S1F1SEC12345_2S0003SEC12345S1F1SEC12345_2S0004SEC4000S4F4SEC4000_2S0004SEC4000S4F4SEC4000_2S0005SEC5000S5F5SEC5000_2S0005SEC5001S5F5SEC5001_2S0005SEC5001S5F6SEC5001_3 Logic is :- If account, security, stage and FBE column value is same then MasterId value should be Security_2 i.e in first row MasterId output should be SEC9954_2 If Account & Security value is same but any other value from stage and FBE is different then masterId value should be incremental. See the row number 5 & 6 masterId value is SEC000399_3 & SEC000399_4 respectively. Please note incremental value will be start with 2. Any duplicates entries where all the column values are same, masterId concate with "_2". Thank you

Update the column with incremental value

Hi,

I have below dataset.

Account	SECURITY	STAGE	FBE
S0001	SEC9954	S1	F1
S0002	SEC000399	S1	F1
S0002	SEC000399	S1	F1
S0002	SEC000399	S1	F1
S0002	SEC000399	S1	F2
S0002	SEC000399	S2	F1
S0003	SEC12345	S1	F1
S0003	SEC12345	S1	F1
S0003	SEC12345	S1	F1
S0004	SEC4000	S4	F4
S0004	SEC4000	S4	F4
S0005	SEC5000	S5	F5
S0005	SEC5001	S5	F5
S0005	SEC5001	S5	F6

My Expected output is MasterId column in below output.

Account	SECURITY	STAGE	FBE	MaserId
S0001	SEC9954	S1	F1	SEC9954_2
S0002	SEC000399	S1	F1	SEC000399_2
S0002	SEC000399	S1	F1	SEC000399_2
S0002	SEC000399	S1	F1	SEC000399_2
S0002	SEC000399	S1	F2	SEC000399_3
S0002	SEC000399	S2	F1	SEC000399_4
S0003	SEC12345	S1	F1	SEC12345_2
S0003	SEC12345	S1	F1	SEC12345_2
S0003	SEC12345	S1	F1	SEC12345_2
S0004	SEC4000	S4	F4	SEC4000_2
S0004	SEC4000	S4	F4	SEC4000_2
S0005	SEC5000	S5	F5	SEC5000_2
S0005	SEC5001	S5	F5	SEC5001_2
S0005	SEC5001	S5	F6	SEC5001_3

Logic is :- If account, security, stage and FBE column value is same then MasterId value should be Security_2 i.e in first row MasterId output should be SEC9954_2

If Account & Security value is same but any other value from stage and FBE is different then masterId value should be incremental. See the row number 5 & 6 masterId value is SEC000399_3 & SEC000399_4 respectively.

Please note incremental value will be start with 2. Any duplicates entries where all the column values are same, masterId concate with "_2".

Thank you

Accepted answers

ShankerV

Hi @alt_tush

Please find the expected output via simple tools. No macro needed.

Many thanks

Shanker V

Update the column with incremental value.yxmd

ShankerV

Hi @alt_tush

Immediate solution via Sort only comes to mind, but please use the Record ID tool before sorting.

So that we can bring back the records in the order where input was with the help of it.

Note: Will try with other tools also, but not sure will it give the correct output in case of large data.

Many thanks

Shanker V

Updated one Update the column with incremental value.yxmd

All comments

ShankerV

Hi @alt_tush

Please find the expected output via simple tools. No macro needed.

Many thanks

Shanker V

Update the column with incremental value.yxmd

alt_tush

Hi Shankar V,

Thank you so much for your quick reply.

The logic which you mentioned it works only my data is in sorting order. If i understand correctly multi row logic comparing the current row with previous row. If the present row is not for same security or account or data is not in sorting order then it might chances of incorrect masterid/output.