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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!