Find unique value in Children

I am looking to determine if a value in a cell of a child is unique among the other children.

 

Order Number |      Items     |   Cost    |  Overall Unique  |  Order Unique  |  Total

      + 100           |                    |               |

            101        |   Oranges   |  $1.00    |             Yes          |          Yes            |  $1.00

            102        |   Apples      |  $2.00    |             Yes          |          Yes            |  $2.00

            103        |  Bananas   |   $3.00    |             Yes          |          Yes            |  $3.00

     +   200          |                    |

            204        |   Pears       |  $1.00     |             Yes         |          Yes            |  $1.00

            205        |  Bananas   |   $5.00    |             No          |          Yes            |  $6.00

            206        |  Bananas   |   $1.00    |             No          |           No            | 

      +  300          |                    |

            300        |   Oranges   |  $2.00    |             No           |          Yes            |  $2.00

            302        |   Apples      |  $1.00    |             No           |          Yes            |  $4.00

            306        |   Grapes      |  $7.00   |             Yes          |          Yes            |  $7.00

            308        |   Apples      |  $3.00    |             No           |           No            |

 

The rows where [Order Number] is 100, 200, and 300 are the PARENT rows, while the other rows (e.g., 101, 102, 103) are the children of the parent. There is a reason the sheet is organized this way, with parents/children.

An item is Overall Unique (=Yes) when it appears the first time in the sheet. Oranges are unique with order 101, but there are not unique overall in order 300. 

An item is Order Unique (=Yes) when it appears the first time among the children in a parent group. Oranges, apples, and bananas are all unique in the orders under 100. Pears and bananas are unique in orders parent group 200, but bananas aren't unique again in order 206. Oranges and grapes are unique in parent group 300, but apples appears twice, so it is only unique once.

I then want to add a column that includes a total of each unique child (when Order Unique = "Yes").

 

I know how to find unique values among all of the rows in the sheet (Overall Unique). I can use the same formula in every row.

=IF(COUNTIF(Items$2:[Items@row, Items@row) <> 1, 0, 1)

I can apply the same kind of formula in each child row, but I have to manually change the start row. So, instead of using $2 across all rows, I have to manually change the $2 to the row of the first child. Not a HUGE deal, but if a row is added, I have to know to change the start row. Whenever I add new orders, I have to remember to change the start row.

Is there any way to set this so I can use the same formula across rows, but Smartsheet knows to compare the Items just among the children, and put Yes if it is a unique item, or No if it is a repeat of a child.

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I have been working on something similar and haven't quite figured it out yet. I'll let you know if I am able to come up with anything.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hello,

     

    Happy to help! After further investigation we believe we've figured out a way to result in 'Yes' and 'No' in both the "Overall Unique" and "Order Unique" columns as desired.

     

    For the "Overall Unique" column, you can use this formula

     

    =IF(NOT(ISBLANK(Items@row)), IF(COUNTIF(Items$1:Items@row, Items@row) = 1, "Yes", "No"))

     

    In order to get the "Order Unique" column to work, another columns has to first be created to show the row's parent value of the "Order Number" column. This also means that the value in each parent row's "Order Number" column has to be unique to that parent row, which by the description it appears it is (We're referring to the Parent values 100, 200, 300).

     

    Steps to take:

     

    1. Create a new column called “Parent”, and fill it with this formula: 

     

    =IF(NOT(ISBLANK(Items@row)), PARENT([Order Number]@row))

     

    2. Fill the “Order Unique” column with this formula:

     

    =IF(NOT(ISBLANK(Items@row)), IF(COUNTIF(COLLECT(Items$1:Items@row, Parent$1:Parent@row, PARENT([Order Number]@row)), Items@row) = 1, "Yes", "No")

     

    Have a wonderful day. Thank you for contacting Smartsheet Support.

    Cheers,

    Eric

    Smartsheet Technical Support

  • Worked perfectly. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Love the COLLECT used within the COUNTIF. I'd have never thought to try that.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Excellent! You and me both! wink

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Love this solution, only issue is that it can't be converted to column formula. Any possible solution to that?? It's now 4+ years later, maybe someone came up with something haha.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!