INDEX/MATCH/COLLECT How to get most recent entry by person and by most recent date
Good afternoon,
I have been spinning my wheels trying to get my formula to work and scouring the community. Hope someone here can help :)
I have employees that need to checkout items and they record this by form. My want is to pull the most recent date based on name and whatever item they checked out.
Sheet A w/ the form has 3 columns - Name (Contact List), Item (text), Created (System Date)"{Create}"
Sheet B has - Name (Contact List), Item (text)
Column I need with a value is "Name of Last Person to Checkout" and that formula so far is:
=INDEX(COLLECT({Name}, {Item}, Item@row), MATCH(MAX(COLLECT({Create}, {Item}, Item@row)), {Create}), 0)
So the formula doesn't fully work because Bob and Joe are showing Invalid Values but Tom is displayed. My thought is I am missing a fundamental piece of function knowledge, syntax, or might need to wrap part of the formula into another function.
Thank you in advance 😀
Best Answer
-
Glad you got things working!
Answers
-
Hi @Needtoworksmart3r, I would use a RowID column instead of the Created By--it accomplishes the same task and it's simpler to work with numbers than date/time. You can then create a helper column (call it HelperColumn) in Sheet A to identify the lines you want with this formula:
=IF(COUNTIFS(Item:Item, Item@row, RowID:RowID, <=RowID@row)=1,"x","")
Then have your formula in Sheet B point to that helper column:
=INDEX(COLLECT({Name}, {Item}, Item@row), {HelperColumn}, "x"),1)
-
The sheet A is linked to the form so there will be multiple entries of the employees with also the same items but with different dates. Some items are checked out more than once per day so that is why I was using MAX with the created date (not created by).
-
@Lucas Rayala Do you know how I would incorporate the most recent entry by person by item. There will be many repeat entries and the only thing that will be different is the date.
-
@Needtoworksmart3r, I meant Created On, not Created By. Because the RowIDs are sequentially created, you can use them to track the first instance. My original post will do what you need.
-
Sorry, I’m having trouble writing my last comment and I realized you were asking for the most recent, and I gave you the formula for the earliest. Do what I said in the first post but but update this formula as follows (just change the greater than symbol to less than):
=IF(COUNTIFS(Item:Item, Item@row, RowID:RowID, <=RowID@row)=1,"x","")
-
@Lucas Rayala You the man! Thank you very much!
-
And just in case someone else finds this, a slight update to the formula for the extra ")"
=INDEX(COLLECT({Name}, {Item}, Item@row, {HelperColumn}, "x"), 1)
-
Glad you got things working!
-
To make use of your great idea of MAX(COLLECT({Created}, {Item}, Item@row)), you could make it work with modifications like these.😃
Your formula:
=INDEX(COLLECT({Name}, {Item}, Item@row), MATCH(MAX(COLLECT({Create}, {Item}, Item@row)), {Create}), 0)
Modified formula:
- =INDEX(COLLECT({Name}, {Item}, Item@row, {Create}, MAX(COLLECT({Create}, {Item}, Item@row))), 1)
- =JOIN(COLLECT({Name}, {Item}, Item@row, {Create}, MAX(COLLECT({Create}, {Item}, Item@row))))
-
Thanks! I was so close
-
Can somebody please help with the below formula, i am wanting the latest entry result in column Pre Start Check Current Hours.. The current formula i have is
=INDEX({Pre Start Check current hours}, MATCH([Rego/ EC]@row, {Pre Start Check Rego Number}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!