JOIN/COLLECT - Order by most recent

I’ve configured a Smartsheet process—fed by Data Shuttle—that supports multiple activity rows per consumer.

Each row calculates a unique Row ID and helper flags (Occurrences, Duplicate Entry, Duplicate IDs) and builds a Detail Key that concatenates Item, Supply Count, Date, and Person.

Using these helpers, the sheet joins and collects all Detail Keys for the same consumer into a single History column, giving us a readable, chronological roll‑up per consumer without losing the underlying row detail.

=IF(Occurrences@row > 1, JOIN(COLLECT([Item, Days Supply, Date, Person]:[Item, Days Supply, Date, Person], MEMBERID:MEMBERID, MEMBERID@row , [Duplicate Entry]:[Duplicate Entry], 1), CHAR(10)), [Item, Days Supply, Date, Person]@row )

Because the raw source data is not sorted prior to upload, the information in the History field is listed oldest to newest.

I have tried this formula as well

=IF(Occurrences@row > 1, INDEX(COLLECT([Item, Days Supply, Date, Person]:[Item, Days Supply, Date, Person], MEMBERID:MEMBERID, MEMBERID@row , [Duplicate Entry]:[Duplicate Entry], 1), CHAR(10)), [Item, Days Supply, Date, Person]@row )

How can I change how this is sorted so that the most recent date is at the top.

Please note: I am unable to provide a screenshot as it is data I cannot share.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Ok. And you want it sorted the other way around?

The easiest way to do this would be to resort the sheet. There is no way to sort the JOIN function, so it brings data in in the same order that it is in the source. The API, the premium add-on Bridge, or other similar third party apps can manage automatic sorting if you have access to those. Otherwise it is a manual operation.

If you do not want to resort the sheet, you can use a second sheet.

On the source sheet, you will need a solution that outputs a number on every row. If you are not already using the Auto-Number column, you can insert this column and not worry about any special formatting. If you are already using the column and have special formatting applied such as prefix, suffix, and/or letters, you would insert a text/number column and use this column formula:

=MATCH([Auto-Number Column Name]@row, [Auto-Number Column Name]:[Auto-Number Column Name], 0)

Then in the secondary sheet, you would have a text/number column (called "Number" in this example. You would manually pre-populate the numbers one 1 through however many you think you will need. I like to also add a bit of a buffer just in case too. If my import is usually between 450 and 500, I would go to 600. If I know it will never go above 500, I would still go to 600 just in case. That sort of thing.

Then you would insert another text/number column called "Row #". This would use this column formula:

=IFERROR(LARGE({Source Sheet Row # Column}, Number@row), "")

Then in the next column over you can use an INDEX/MATCH to bring over the [Item, Days Supply, Date, Person] column data:

=IF([Row #]@row <> "", IFERROR(INDEX({Source Sheet Item, Days Supply, Date, Person Column}, MATCH([Row #]@row, {Source Sheet Row # Column}, 0)), ""))

Now that the sort has been flipped, using your JOIN/COLLECT on this second sheet should get you the results you want.

All comments

Paul Newcome

Are you able to provide a screenshot with mock data such as Mickey Mouse and Donald duck for people, Item 1 and Item 2 for items, 1111 and 2222 for ids, etc.?

Marlana K.

Hi @Paul Newcome

I was hoping you would answer me! This is dummy data with information changed.

Paul Newcome

Ok. And you want it sorted the other way around?

If you do not want to resort the sheet, you can use a second sheet.

=MATCH([Auto-Number Column Name]@row, [Auto-Number Column Name]:[Auto-Number Column Name], 0)

Then you would insert another text/number column called "Row #". This would use this column formula:

=IFERROR(LARGE({Source Sheet Row # Column}, Number@row), "")

Then in the next column over you can use an INDEX/MATCH to bring over the [Item, Days Supply, Date, Person] column data:

=IF([Row #]@row <> "", IFERROR(INDEX({Source Sheet Item, Days Supply, Date, Person Column}, MATCH([Row #]@row, {Source Sheet Row # Column}, 0)), ""))

Now that the sort has been flipped, using your JOIN/COLLECT on this second sheet should get you the results you want.

Naeem Ejaz

I am unable to comment without viewing your current setup.

Marlana K.

@Naeem Ejaz Unfortunately due to confidential & sensitive information contained in my solution I will not be able to share the sheets. Company policy even considers the column names as items that cannot be shared or viewed beyond minimum necessary.

Marlana K.

@Paul Newcome

Updated Findings:
I tested re‑sorting the Excel data, cleared the original data from the target sheets, and then added the newly sorted file into the repository sheet. Unfortunately, even with the Data Shuttle action set to Add (so it should append data rather than overwrite it), it’s no longer bringing over all rows.

One file should contain 5,684 entries, but the Data Shuttle only transferred 624. Another file that should have 1,362 entries now only shows 157 in the sheet. It appears the process is now only pulling in a single occurrence of each row rather than the full dataset.

I ended up running with Replace which populated the sheet yet produced an error, and an Aw, Snap something went wrong while displaying this webpage, error code out of memory. These formulas were not set in until after the initial load of data and I did not get that error.

Back to the drawing board I think this is going to require a data table for both Pharmacy Item tabs. I welcome any suggestions you have.

Paul Newcome

Try removing the formulas then using data shuttle to replace all rows with the new file sorted the way you want or sorting the sheet after you import using data shuttle. If the data is sorted the way you want it, you don't need the formulas from my previous post.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.