Ignore duplicate child rows and show data of only the latest date effective and lowest price

Hi guys,

I'm pulling my hair out trying to solve this one.

I have created a price file of items in smartsheet. Each Item can be purchased from multiple service providers and I am keeping a history of what the item cost per service provider at different times.

I would like to be able to summarise each item at the parent level to identify which service provider can supply the item at the lowest price. Because I'm keeping historic pricing, I need to ignore service provider pricing that has been superseded by a more current date effective.

Can anyone tell me how to do this? I've included a screenshot of my sheet where the formula should return the result: Res Direct, $1.22, 2/12/2022.

Find more posts tagged with

Accepted answers

Enfield Homes

Please ignore @Paul Newcome, Looks like I've solved it with the below formula.

=IFERROR(INDEX(COLLECT(CHILDREN([Service Provider:]@row), CHILDREN(Available@row), 1, CHILDREN([Purchase Rate]@row), Min@row), 1) + ", " + "$" + Min@row, "")

All comments

Paul Newcome

You would need a helper column for this one.

Checkbox column called "Most Recent" with the following column formula:

=IF([Date Effective]@row = MAX(COLLECT([Date Effective]:[Date Effective], Description:Description, @cell = Description@row, [Service Provider:]:[Service Provider:], @cell = [Service Provider:]@row)), 1)

Then in the parent rows of the Purchase Rate column you would use:

=MIN(COLLECT(CHILDREN(), CHILDREN([Most Recent]@row), @cell = 1))

Enfield Homes

Thanks @Paul Newcome,

I've decided to add an archive checkbox to eliminate historic pricing from the formula. When I add a new price for a service provider I will check the archive box for the previous price. This will allow me to keep a history while excluding it from live pricing.

I'm then left with only two criteria:

Find the min price
Is the product available (Archived product = 0, available product =1)
The final step would be to match both of the above criterial and then return the supplier and price that matches.

Here's my formula so far, which I know isn't quite right.

=INDEX(COLLECT(CHILDREN([Service Provider:]@row), CHILDREN(Available@row), 1, (CHILDREN([Purchase Rate]@row), Min@row))

The result should equal: 'Westland Landscaping'. I'll then use the JOIN function to add the price to the end

Enfield Homes

Please ignore @Paul Newcome, Looks like I've solved it with the below formula.

=IFERROR(INDEX(COLLECT(CHILDREN([Service Provider:]@row), CHILDREN(Available@row), 1, CHILDREN([Purchase Rate]@row), Min@row), 1) + ", " + "$" + Min@row, "")

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.