Update/Replace column values if the column contains certain value

[mjh418]

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.

[atcodedog05]

Hi @mjh418 

Here is how you can do it.

Workflow:

Hope this helps : )

[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

[atcodedog05 · Accepted solution]

Hi @mjh418 

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

Workflow:

Hope this helps : )

[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!

[mjh418]

@atcodedog05 Fantastic! Thank you very much for the help.