Calculation value based on multiple conditions

[sophievanluin]

Hi there,

I would like to make a calculation based on the columns below:

Project ID	Managing Office System	Managing Office Postal
1234	Amsterdam
1234	Amsterdam
1234	Amsterdam
5678	Rotterdam
5678	Amsterdam
9101	Rotterdam
9101	Rotterdam
4321	Amsterdam
4321	Rotterdam
4321	The Hague
4321	Rotterdam

This data is just a small example of a larger dataset, but the idea is the same.

I would like to calculate the 'Managing Office Postal" based on the "Project ID" and the "Managing Office System".

• For every Project ID where the Managing Office System is the same, the Managing Office Postal should remain the same as the mentioned city.
• For every Project ID where the Managing Office System is not the same, the Managing Office Postal should change to "National"

Example results:

Project ID	Managing Office System	Managing Office Postal
1234	Amsterdam	Amsterdam
1234	Amsterdam	Amsterdam
1234	Amsterdam	Amsterdam
5678	Rotterdam	National
5678	Amsterdam	National
9101	Rotterdam	Rotterdam
9101	Rotterdam	Rotterdam
4321	Amsterdam	National
4321	Rotterdam	National
4321	The Hague	National
4321	Rotterdam	National

How do I integrate this in my workflow? Thanks in advance.

[atcodedog05 · Accepted solution]

Hi @sophievanluin 

Here is how you can do it.

Workflow:

Hope this helps : )

[JaeLim]

HIi @sophievanluin 

Please see my take on this. I mainly utilized the count distinct action in the "summarize tool" to see how many different managing office systems per Project ID. 

Hope this helps!

[atcodedog05]

Happy to help : ) @sophievanluin 

Cheers and have a nice day!