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

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    edited 03/22/22 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 Co., LTD - Smartsheet Solution Partner.

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    edited 03/22/22 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 Co., LTD - 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🤗

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    I am happy to help.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!