Index/Match - Return the lowest row for multiple matches

Amanda Merrigan

Smartsheet-Discard Snip.JPG

Smartsheet-Wetcast Snip.JPG

I have an automation created that copies the row from the "Wetcast" sheet to a "Discard" sheet. From the Discard sheet, I want to return the "Ready to Ship Date" at the bottom most row into the "Previous Ready to Ship Date" cell in the Wetcast Sheet. The identifier/match value is the "ID" (GWC####).

This is the formula that I've come up with for the Previous Ready to Ship Date.

=INDEX({Ready to Ship - Discard}, MATCH(ID@row, {ID-Discard}, 0), 0)

However, it does not return the value that I wanted. Based on GWC1487, I would like to see 18-Sep-2020 returned as it's the last value on the sheet (meaning the last date change that was moved over) but the formula above is returning 21-Sep-2020 which is the higher row. I've tried changing the search type in the Match formula, but it either returns a #nomatch with -1 or blank with a 1.

Anyone have any ideas!?

Paul Newcome

I'm not quite sure of the reason for the holder sheet.

How about this...

Insert a column on the Discard sheet, enter the below formula into row 1 and then dragfill it down the rest of the rows (auto-fill should grab it for rows added later).

=COUNTIFS(ID$1:ID@row, OR(@cell = "", @cell <> ""))

This will replicate the row number.

Next we go back to the Westcast sheet and use this...

=INDEX({Discard Sheet Date Column}, MAX(COLLECT({Discard Sheet Row Number Column}, {Discard Sheet ID Column}, ID@row, {Discard Sheet Date Column}, @cell <> ""))

Once we generate the usable row numbers, we can use the MAX/COLLECT function to pull the highest row number for that particular ID where the date column is not blank and use that as the row number for the INDEX function to pull the date from.

Amanda Merrigan

Thanks Paul!

We actually brainstormed this afternoon and I think we figured it out! We deleted the system generated column for the ID and combined a couple columns to get a unique ID for the row. Then we created an auto number column for the Row ID in the Discard sheet. Then your initial Index/Match/Collect formula above works like a charm!!

Amanda Merrigan

Here are snips of the files I attached - for some reason I can't do it on the original post. The first image is the "Westcast" Sheet and the second is the "Discard".

Also - sorry for the last of spaces...it re-formatted when I posted!

Paul Newcome

DO you have a column that tracks the date when the row is copied such as a system generated Created Date column on the Discard Sheet?

Amanda Merrigan

Hi Paul - yes, we do...but the problem is that we could have multiple lines copied over on the same day (our production schedule can change a LOT!) and we would still need the last one. Also - and this might get tricky to understand - we have a 3rd sheet involved that is a "holder" sheet. When a date changes it gets moved from Wetcast to the holder sheet and then after 1 day (and our new & previous ship date emails are sent) it gets moved to the discard sheet. If we moved directly from Wetcast to Discard then it would show the same Ready to Ship Date as the Wetcast...when we actually need the previous date. And when the dates move from the Holder sheet to the Discard sheet they all have the same date/time because of the automation created to move them.

Clear as mud!? lol

Amanda Merrigan

Yes - we have the system generated column in our sheet, but figured out we can't use it (at least, we don't think so!) as we could have multiple changes in one day. And to make things more complicated we actually don't move items directly from Wetcast to Discard...we have a holder sheet that holds the changes for one day before it is moved to the Discard sheet. If we didn't do this - then the ready to ship date pulling from the Discard sheet would also be the same as the one on the Wetcast sheet since it moves when it changes. We will set up a notification that sends out every evening when a date changes - capturing the actual previous date before the holder sheet moves the row to the discard sheet...therefore changing the previous ready to ship date to what it currently is. It's not perfect...but it's what we could figure out. In any case - the created date on the Discard sheet will be the same for all the rows that move in a day because of the automation created to get them from the holder sheet.

Clear as mud?! lol

Amanda Merrigan

If you know of a way to pick the second last date/time or the second last row match then we could eliminate that holder sheet! We're just really struggling with multiple match values.

Paul Newcome

I'm not quite sure of the reason for the holder sheet.

How about this...

Insert a column on the Discard sheet, enter the below formula into row 1 and then dragfill it down the rest of the rows (auto-fill should grab it for rows added later).

=COUNTIFS(ID$1:ID@row, OR(@cell = "", @cell <> ""))

This will replicate the row number.

Next we go back to the Westcast sheet and use this...

=INDEX({Discard Sheet Date Column}, MAX(COLLECT({Discard Sheet Row Number Column}, {Discard Sheet ID Column}, ID@row, {Discard Sheet Date Column}, @cell <> ""))

Once we generate the usable row numbers, we can use the MAX/COLLECT function to pull the highest row number for that particular ID where the date column is not blank and use that as the row number for the INDEX function to pull the date from.

Amanda Merrigan

We thought about doing something like that, but haven't had success with formulas on sheets where they were automatically added to the end. The formula wouldn't autofill. Will give it another try though!

Paul Newcome

I personally use this setup in a number of different processes. If the rows are being automatically added either through automations or form submissions, then auto-fill should be able to handle it pretty well.

Amanda Merrigan

This formula & additional column worked perfectly!! I'll just have to see if the formula autofills after the next one is added. Fingers & toes crossed!!!

Thanks for your help 😊

Amanda Merrigan

Hey Paul - unfortunately, the formula didn't carry down for new lines that were added by automation last night. This is why we didn't go the formula route before. Any ideas?

Paul Newcome

Are the new rows being added at the same hierarchy level as the last row that was already in the sheet? Having the same hierarchy is part of the requirement for auto-fill to work.

Andrée Starå

Hi @AmandaM

To add to Paul’s excellent answers.

The new row will be auto-filled with the formulas if there are two rows below/above with the same formulas and the same level of hierarchy.

More details:

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

More info: 

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Amanda Merrigan

Thanks Paul & Andree - that's the issue. When we auto copy the rows, it copies the child lines as well, then the hierarchy doesn't match when new rows come in. Any other ideas? We don't need the child lines here - is there a way to just bring in parent lines?

Paul Newcome

You would need to Incorporate a checkbox type column that would automatically have the box checked on parent rows.

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, 1)

Then set up your automation to only copy rows where that box is checked.

Amanda Merrigan

We already have a hierarchy column that gives us 0s and 1s (and sometimes 2s!) - formula below - we have tried automating off that but it still pulls the children with it.

=COUNT(ANCESTORS())