Find unique value in Children

Art Schneiderheinze

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.

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.

Eric M Oliveira

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

Art Schneiderheinze

Worked perfectly. Thanks!

Paul Newcome

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

Andrée Starå

Excellent! You and me both! 

Melisa Dannhauser

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.