Formula to collect the first and last values from children rows, but only if distinct.


Hello Community People,

I'm working to create a report that will show a summary of the location where a person will be each day. The source sheet is set up with parent rows listing the date and child rows showing all the events scheduled for that date, and there is a single drop-down "Location" column to select the city in which the event takes place. I've used JOIN, COLLECT, and mixed in little bit of DISTINCT to my formula with only limited success.

Ideally, if all the events for a particular day are in the same location, then I would like the result to simply be that location. However, if there are different locations for the same day, the I would like the result to list the first location and the last location with a deliminater.

For example, if there are five events in a day and the chronological order of their locations are New York, New York, New York, New York, and New York, the result should just be "New York". If the five events for a day are located in New York, New York, London, London, and London, then the result would be "New York to London". Here is my formula in its latest version:

=IF(Level@row = 0, JOIN(DISTINCT(COLLECT(CHILDREN(Location@row), CHILDREN(Location@row), <>""), " to ")))

Any help would be appreciated. Thanks.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!