Comparing Columns to Identify Health
Hello,
I'm really struggling here and I'm sure I can do something simple but I'm just not getting it.
Here's my task: Identify if any of our projects, which happen to overlap sometimes, are installing same version of software. If they are not, I need to flag that. I'm currently using "Green" and "Red" traffic lights for that.
In the image below. I want to check that "Version Installed per Deployment" is equal to " OEMV Credit Package Version" equal to "Chase FF Credit Package Version", equal to "vStore Credit Package Version". If they all are (and ignore blanks), then we get a Green Light. If there is a mismatch like the second row, then we should have Red Light.
I've worked through different formulas in every different way as well as adding other columns to help me identify as a number to be able to subtract (trying to think outside the box).
I'd appreciate the help.
Best Answer
-
Hi @ltomasello,
The idea is to count the distinct of versions in a row (ignore blanks).
If count = 0 then flag is "blank"
If count = 1 then flag is "green"
Else flag is "Red"
Try this formula in the Flag column:
=IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 0, "", IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 1, "Green", "Red"))
Hope that helps.
Gia Thinh Technology - Smartsheet Solution Partner.
Answers
-
Hi @ltomasello,
The idea is to count the distinct of versions in a row (ignore blanks).
If count = 0 then flag is "blank"
If count = 1 then flag is "green"
Else flag is "Red"
Try this formula in the Flag column:
=IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 0, "", IF(COUNT(DISTINCT(COLLECT([Version Installed per Deployment]@row:[vStore Credit Package Version]@row, [Version Installed per Deployment]@row:[vStore Credit Package Version]@row, <>""))) = 1, "Green", "Red"))
Hope that helps.
Gia Thinh Technology - Smartsheet Solution Partner.
-
Hi Gia,
Thank you so much. This totally did it. I would have not been able to come up with this. I've learned something new today.
Thank you🤗
-
I am happy to help.
Gia Thinh Technology - Smartsheet Solution Partner.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!