INDEX/MATCH/COLLECT How to get most recent entry by person and by most recent date

Needtoworksmart3r

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 😀

Lucas Rayala

Glad you got things working!

Lucas Rayala

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)

Needtoworksmart3r

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).

Needtoworksmart3r

@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.

Lucas Rayala

@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.

Lucas Rayala

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","")

TrevRCincySheetz

@Lucas Rayala You the man! Thank you very much!

TrevRCincySheetz

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)

Lucas Rayala

Glad you got things working!

jmyzk_cloudsmart_jp

Hi @Needtoworksmart3r

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

Needtoworksmart3r

@jmyzk_cloudsmart_jp

Thanks! I was so close

Alexandra Amies

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