Index/Match - Return the lowest row for multiple matches
Answers
-
How do you have your automation set up?
-
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.
-
@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?
-
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.
-
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.
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)?
-
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))
-
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.
-
That's really odd. I tested it before posting and it worked fine for me. Hmm... Back to the drawing board...
-
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...
-
It certainly does! My boss and I have been at this (off and on) for months and have tried so many things!
-
I have an idea, but I am definitely going to have to do some digging through some notes and testing. Hang in there. Haha.
-
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.
-
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!!
-
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.
-
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!