Update/Replace column values if the column contains certain value

Hi All,

Would greatly appreciate if you can help me with the simplest solution for below:

There are items that belong to the Main Category (e.g. 100 and 200) such as XXX and AAA. There are also SUB-category items that share same main category code but have different sub-category codes. I want to replace/update the [Status] of the sub-items that belong in the same Main Category column with a "yes" IF there is YES in the status of the main category items. IF not Yes, then they can remain as Nulls. Please see below for sample and DESIRED result:

Sample:

Date	Item	Main Category	Sub Category	Status
Jan	XXX	100	100	YES
Jan	XAA	100	110	[Null]
Jan	XBB	100	120	[Null]
Jan	YYY	200	200	NO
Jan	YAA	200	210	[Null]
Jan	YBB	200	230	[Null]
Feb	...	...	...	...

Desired Result:

Date	Item	Main Category	Sub Category	Status
Jan	XXX	100	100	YES
Jan	XAA	100	110	YES
Jan	XBB	100	120	YES
Jan	YYY	200	200	NO
Jan	YAA	200	210	[Null]
Jan	YBB	200	230	[Null]
Feb	...	...	...	...

Thank you so much for your time and help.

Let me know if need clarification.

Workflow

Datasets

Output

Tips and Tricks

Dynamic Processing

Text Mining

Accepted answers

atcodedog05

Hi @mjh418

Here is the updated solution. You would need to Date also in the groupby and joining.

Workflow:

Hope this helps : )

Status.yxmd

All comments

atcodedog05

Hi @mjh418

Here is how you can do it.

Workflow:

Hope this helps : )

Status.yxmd

mjh418

Hi @atcodedog05 ,

You are always so helpful, thank you.

But would this work if there are other months(dates) that should be treated separately? For example, in January 100 might be YES, but in February it might not be.

Nanoq

Here you go, you can use a multi row formula to do your logical check, just make sure that the top row is the one identifying the main category

if you need to treat months separately, just use the group my feature, and check off "date"
Let me know if this does what you want

mjh418

Thank you @Nanoq for the help! However, my input is very dynamic and cannot be guaranteed that the first row will be the main category. Thank you anyway, I got the solution!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703