How do I find the closest matching row that is above my searching row?

I am trying to find the closest matching row that is above my searching row.

If Smartsheet does not allow searching backwards (above the current row) is there a way to search downwards, where the closest matching criteria to the searching row is displayed like the formula below is attempting to do?

The following provides an example of what I am trying to do:

If dollars in PM Final Revenue column, copy value to 2501_January and no need to search. Else. search upwards from the current row (call it Row Z) and find the closet matching row (call it Row D) with a JO #, FC Month and look for data in the 2501_January column. If no match found in all the rows above it, let Row Z's 2501_January column remain blank. Else, display the Row D's column 2501_January data into Row Z's 2501_January column.

In this case for Row Z, Row A , Row G and Row C are bypassed. I only want the value directly above the searching row. The following formula was written to perform this action. Unfortunately as it searches from the Parent row down the child rows, it displays the first match instead of looking further for the closet row's matching data. The formula is placed in the 2501_January column. This is why I have a helper column that is an exact match of the 2501_January column so I don't get a Circular Reference error.

=IF([FC Month]@row = "2501_January", [PM Final Revenue]@row , IF(COUNT(CHILDREN()) > 0, "", IFERROR(INDEX(COLLECT([2501_January_Helper]:[2501_January_Helper], [JO #]:[JO #], [JO #]@row , Month:Month, 1), 1), "")))

There's a Month column that is also also looked at. I didn't include it in my screen shot. Its just the corresponding month number to January, i.e., 1.

Find more posts tagged with

Formulas

Accepted answers

Georgie

Hi @mtuccillo,

I tested this on my end and was able to get the following formula working:

=IFERROR(IF([PM Final Revenue]@row <> "", [PM Final Revenue]@row , INDEX(COLLECT([PM Final Revenue]:[PM Final Revenue], [FC Month]:[FC Month], [FC Month]@row , Auto:Auto, MAX(COLLECT(Auto:Auto, [FC Month]:[FC Month], [FC Month]@row , Auto:Auto, <Auto@row ))), 1)), "")

This formula says: If [PM Final Revenue] is not blank, use its value. Otherwise, find the most recent previous row with the same [FC Month] and return its [PM Final Revenue]. If nothing is found, return blank.

For this to work, you’ll need to have an auto-number column set up in your sheet - mine is named “Auto” and starts at 1. This is what the formula uses to find the most recent previous row.

You can see my test sheet set up in the same way as yours and the formula working in the 2501_January column in the screenshot below:

Does that work for you?

Georgie

Hi @mtuccillo,

Nice work building on the formula and testing it! Good spot with the issue when adding new rows in between existing rows.

Adding a new column with =MATCH([Row Key]@row , [Row Key]:[Row Key], 0) is exactly what you need to ensure that row numbers stay sequential when rows are added amongst existing rows.

Your latest formula is almost correct! Two things need to be changed:

We need to add the JO # criteria into the MAX(COLLECT) section as well as into the INDEX(COLLECT) section.
We need to change every instance of “Row Key” to the name of the column that contains your =MATCH([Row Key]@row , [Row Key]:[Row Key], 0) formula.

So, where my column with the above formula is named “Row Number”, my final formula would be:

=IFERROR(IF([PM Final Revenue]@row <> "", [PM Final Revenue]@row , INDEX(COLLECT([PM Final Revenue]:[PM Final Revenue], [FC Month]:[FC Month], [FC Month]@row , [JO #]:[JO #], [JO #]@row , [Row Number]:[Row Number], MAX(COLLECT([Row Number]:[Row Number], [FC Month]:[FC Month], [FC Month]@row , [JO #]:[JO #], [JO #]@row , [Row Number]:[Row Number], <[Row Number]@row ))), 1)), "")

Let me know if that works for you once you change the 'Row Key' references to the name of your column!

Georgie

All comments

Georgie

Hi @mtuccillo,

I tested this on my end and was able to get the following formula working:

=IFERROR(IF([PM Final Revenue]@row <> "", [PM Final Revenue]@row , INDEX(COLLECT([PM Final Revenue]:[PM Final Revenue], [FC Month]:[FC Month], [FC Month]@row , Auto:Auto, MAX(COLLECT(Auto:Auto, [FC Month]:[FC Month], [FC Month]@row , Auto:Auto, <Auto@row ))), 1)), "")

For this to work, you’ll need to have an auto-number column set up in your sheet - mine is named “Auto” and starts at 1. This is what the formula uses to find the most recent previous row.

You can see my test sheet set up in the same way as yours and the formula working in the 2501_January column in the screenshot below:

Does that work for you?

Georgie

mtuccillo

Wow! Let me test and get back to you. I cant express enough my thanks for your time in looking into this.

mtuccillo

Looks promising although I need it to search by the "FC Month" and "JO #". I dont see the "JO #" in the formula. Would it be too much trouble to add that criteria to the formula?

mtuccillo

Is this the correct way to add it?

=IFERROR(IF([PM Final Revenue]@row <> "", [PM Final Revenue]@row , INDEX(COLLECT([PM Final Revenue]:[PM Final Revenue], [JO #]:[JO #], [JO #]@row , [FC Month]:[FC Month], [FC Month]@row , [Row Key]:[Row Key], MAX(COLLECT([Row Key]:[Row Key], [FC Month]:[FC Month], [FC Month]@row , [Row Key]:[Row Key], <[Row Key]@row ))), 1)), "")

mtuccillo

Okay I tested it and it works great. Until I add a row between the other rows. Then it ignores it. The problem is that when I add a new row, the rows are no longer sequential. In order for this to work, I need something that will "look at" the sequential row number for when new rows are added. Is there a way to enhance auto@row so it represents the row # it resides on? Below is an example of what I found after adding a row (F) to the table. In this example, the new Row F shows an auto # of 567 which is not sequential for the search. If I can look at the sequential row number, this will work!

mtuccillo

And I think I found the forumula to provide the sequential row # for when there are cases when the rows are added in between.

=MATCH([Row Key]@row , [Row Key]:[Row Key], 0)

Where [Row Key]@row is an auto number.

I think that's it!

mtuccillo

I need it to search by the "FC Month" and "JO #". And for FC Month a specific 2501_January value. I don't see the "JO #" in the formula. Here's what I've done so far but the search isn't finding previous jobs like it should so maybe I have the syntax incorrect?

=IFERROR(IF([PM Final Revenue]@row <> "", [PM Final Revenue]@row , INDEX(COLLECT([PM Final Revenue]:[PM Final Revenue], [JO #]:[JO #], [JO #]@row , [FC Month]:[FC Month], "01_January" , [Row Key]:[Row Key], MAX(COLLECT([Row Key]:[Row Key], [FC Month]:[FC Month], [FC Month]@row , [Row Key]:[Row Key], <[Row Key]@row ))), 1)), "")

Where I only look backwards for FC Month = 2501_January and the [JO #] is added to the search criteria.

Georgie

Hi @mtuccillo,

Nice work building on the formula and testing it! Good spot with the issue when adding new rows in between existing rows.

Adding a new column with =MATCH([Row Key]@row , [Row Key]:[Row Key], 0) is exactly what you need to ensure that row numbers stay sequential when rows are added amongst existing rows.

Your latest formula is almost correct! Two things need to be changed:

We need to add the JO # criteria into the MAX(COLLECT) section as well as into the INDEX(COLLECT) section.
We need to change every instance of “Row Key” to the name of the column that contains your =MATCH([Row Key]@row , [Row Key]:[Row Key], 0) formula.

So, where my column with the above formula is named “Row Number”, my final formula would be:

=IFERROR(IF([PM Final Revenue]@row <> "", [PM Final Revenue]@row , INDEX(COLLECT([PM Final Revenue]:[PM Final Revenue], [FC Month]:[FC Month], [FC Month]@row , [JO #]:[JO #], [JO #]@row , [Row Number]:[Row Number], MAX(COLLECT([Row Number]:[Row Number], [FC Month]:[FC Month], [FC Month]@row , [JO #]:[JO #], [JO #]@row , [Row Number]:[Row Number], <[Row Number]@row ))), 1)), "")

Let me know if that works for you once you change the 'Row Key' references to the name of your column!

Georgie

mtuccillo

Perfect! Thank you so much. I thought I was a gonner since this is so complex. Thank you again for spending the time to help out.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.