Index/Match - Return the lowest row for multiple matches

Paul Newcome

How do you have your automation set up?

Amanda Merrigan

I didn't currently have it set up this way - as it didn't seem to work for us - but this is how I would set it up. I'll leave it and keep an eye on it again to see how it performs.

Paul Newcome

@Andrée Starå and @Genevieve P

Is it expected behavior" for a copy/move row automation to also pull child rows even if they don't meet the criteria if the parent row does meet the criteria?

Amanda Merrigan

The Ready to Ship Date (which triggers the copy) isn't even on the child rows. I just made a change and you can see here that it brought over parent and child rows.

Amanda Merrigan

I just found this discussion - looks like there isn't a way to copy just a parent row. This is great in some cases, but obviously not in others. I'll put through an enhancement request to be able to pick the option of copying just parent or include children in the Workflow Generator.

https://community.smartsheet.com/discussion/65473/how-to-copy-a-parent-row-through-automation-without-also-receiving-children

In the meantime - any other ideas for a work around to my scenario!? lol

Is there not an option to select Collect/Match/Index/something...and pick the value from the lowest row? Reverse the VLOOKUP (since it always gives the value at the top)?

Paul Newcome

Hmm... Lets try this...

Insert a system generated Created (date) column on the Discard sheet.

Then you should be able to use this in the Wetcast sheet:

=INDEX({Discard Sheet Ready To Ship Date Column}, MATCH(MAX(COLLECT({Discard Sheet Created Column}, {Discard Sheet ID Column}, ID@row)), {Discard Sheet Created Column}, 0))

Amanda Merrigan

We've tried something similar to this as well, but couldn't get consistent results and sometimes we got a date that we have no idea where it came from! I just tried your formula and again, got a date that shouldn't even be an option!

Here are all the items in the Discard sheet for the ID GWC1308. We're expecting an Oct 2 return...however, the formula is producing Sept 28, which isn't even an option!

Here are the rows that have Sept 28 as their Ready to Ship Date.

Paul Newcome

That's really odd. I tested it before posting and it worked fine for me. Hmm... Back to the drawing board...

Paul Newcome

Now I see why and it makes sense, so definitely back tot he drawing board. Not having formulas on the Discard sheet certainly makes it quite a challenge...

Amanda Merrigan

https://community.smartsheet.com/discussion/comment/262173#Comment_262173

It certainly does! My boss and I have been at this (off and on) for months and have tried so many things!

Paul Newcome

I have an idea, but I am definitely going to have to do some digging through some notes and testing. Hang in there. Haha.

Paul Newcome

Ok. Lets give this a go...

Move this to its own Date type column [Helper Date]:

=MAX(COLLECT({Discard Sheet Created Column}, {Discard Sheet ID Column}, ID@row))

Then use this for your final formula:

=INDEX(COLLECT({Discard Sheet Ready To Ship Date Column}, {Discard Sheet Created Column}, DATEONLY(@cell) = [Helper Date]@row, {Discard Sheet ID Column}, ID@row), 1)

If that doesn't work I have another idea where we can leverage your Modified (date) and Created (date) columns using the same principles.

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!!

Paul Newcome

Great! I never use the Auto-number, so I had completely forgotten about it. Glad you were able to get it working! 👍️

Please don't forget to mark your solution as "helpful" or the "accepted answer" so that others searching for a similar solution can know that one may be found here.

Andrée Starå

https://community.smartsheet.com/discussion/comment/262133#Comment_262133

Happy to help!