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

Lindsay P.

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.

Paul Newcome

Try something like this:

=IF(Level@row = 0, INDEX(CHILDREN(Location@row), 1) + IF(COUNT(DISTINCT(CHILDREN(Location@row))) > 1, " to " + INDEX(CHILDREN(Location@row), COUNT(CHILDREN(Location@row)))))

Paul Newcome

Are you able to provide a screenshot for reference? Sample data is fine.

Lindsay P.

Certainly. Here's an example schedule where I would like the Location Summary for 1/01/23 to be "New York", for 1/02/23 it would be "New York to Chicago", and for 1/03/23 it would be "Chicago". This would automate the reports I need to generate that give an overview of where the person is each day. As you can see, the formula is only partially working.

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

Paul Newcome

Try something like this:

=IF(Level@row = 0, INDEX(CHILDREN(Location@row), 1) + IF(COUNT(DISTINCT(CHILDREN(Location@row))) > 1, " to " + INDEX(CHILDREN(Location@row), COUNT(CHILDREN(Location@row)))))

Lindsay P.

That worked! Thanks for working your magic on my behalf.

Paul Newcome

Happy to help. 👍️