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.
Email : thinh.huynh@giathinh.tech
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.
Email : thinh.huynh@giathinh.tech -
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.
Email : thinh.huynh@giathinh.tech
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!