Hi all, I've been trying to update a column, but I've been stuck for a while and could use some advice on how to move forward to fix this. Sl.noNAMETYPEPREOPREQUIRED Output1BOTTLE C1C12BOTTLE E1E13BOTTLE E2E24BOTTLE E2.A1E2.A15BOTTLE D1D16BOTTLE E1.A1E1.A17BOTTLE E1.A2E1.A28BOTTLEEA1 E4.A19BOTTLE E3E310BOTTLECA1 C2.A111BOTTLECA1 C3.A112BOTTLEA E1.A1.P1E1.A1.P113CAR C1C114CAR C2C215CAR E1E116CAR E2E217CARC E2.b1E2.b118CARC E2.D1E2.D119CARC E1.A1E1.A120CAR D1D121CAR A1A122CAR E3E323CARAq1 A2.Q1 For the missing fields, I want to update them by determining the highest value of the same [TYPE] in the same [NAME], incrementing it accordingly, and concatenating it with the values in [PRE]. EX: In row 8 , we find the type is E and the max value is E3 and hence the op column will be filled as "E4. " concatenating with corresponding PRE i.e., E4.A1 In row 10 &11 we see that the type is c and hence when updating it should be c2 and c3 . Thanks in advance!

column update based on various criteria.

Hi all,

I've been trying to update a column, but I've been stuck for a while and could use some advice on how to move forward to fix this.

Sl.no	NAME	TYPE	PRE	OP	REQUIRED Output
1	BOTTLE			C1	C1
2	BOTTLE			E1	E1
3	BOTTLE			E2	E2
4	BOTTLE			E2.A1	E2.A1
5	BOTTLE			D1	D1
6	BOTTLE			E1.A1	E1.A1
7	BOTTLE			E1.A2	E1.A2
8	BOTTLE	E	A1		E4.A1
9	BOTTLE			E3	E3
10	BOTTLE	C	A1		C2.A1
11	BOTTLE	C	A1		C3.A1
12	BOTTLE	A		E1.A1.P1	E1.A1.P1
13	CAR			C1	C1
14	CAR			C2	C2
15	CAR			E1	E1
16	CAR			E2	E2
17	CAR	C		E2.b1	E2.b1
18	CAR	C		E2.D1	E2.D1
19	CAR	C		E1.A1	E1.A1
20	CAR			D1	D1
21	CAR			A1	A1
22	CAR			E3	E3
23	CAR	A	q1		A2.Q1

For the missing fields, I want to update them by determining the highest value of the same [TYPE] in the same [NAME], incrementing it accordingly, and concatenating it with the values in [PRE].

EX: In row 8 , we find the type is E and the max value is E3 and hence the op column will be filled as "E4. " concatenating with corresponding PRE i.e., E4.A1

In row 10 &11 we see that the type is c and hence when updating it should be c2 and c3 .

Thanks in advance!

Input

Best Practices

Data Investigation

Accepted answers

FinnCharlton

Hi @QurrathAmeena

This workflow achieves your desired output if I understand it correctly.

Column Update.yxmd

OllieClarke

Hi @QurrathAmeena

Here's a way of doing it:

Hope that helps,

Ollie

Column Update.yxmd

OllieClarke

Hi @ShankerV

I don't think the multi-field formula tool can in fact solve this problem, but regardless I don't think your reply is really that helpful - it's incredibly vague and doesn't really answer the question.