Formula help : Identifying most recent row that meets specific criteria

I am recording contents moving between sites based off a SKU. I want to write a formula in the column shown below "Most Recent" that will identify the row as the most recent row for that specific sku. I'm guessing I need a max or collect formula but can't quite figure out how to write it. Concern with MAX - I could have 5 rows added within a minute that would display different sku numbers and different statuses.
Best Answer
-
You would need to insert an auto-number column with no special formatting (called "Auto" in the below example). Then you can use this in a checkbox column:
=IF(Auto@row = MAX(COLLECT(Auto:Auto, SKU:SKU, @cell = SKU@row)), 1)
Answers
-
You would need to insert an auto-number column with no special formatting (called "Auto" in the below example). Then you can use this in a checkbox column:
=IF(Auto@row = MAX(COLLECT(Auto:Auto, SKU:SKU, @cell = SKU@row)), 1)
-
Thanks for your help!
-
Happy to help. 👍️
-
I have a variation of this that I'm hoping you may be able to help with. Same philosophy but reviewing 5 columns for the same number and only returning the value if it is the newest row. Thought process here is we are picking up 5 barcoded boxes, transporting them offsite then scanning barcodes when received. The order will vary from pickup to drop off. If I write the formula as below, I get an #incorrect argument error unless the item only appears in Barcode 1: Barcode 5.
If I modify the formula to not reference Barcode 1: Barcode 5 under the Max formula, it works, however it only considers the current column.
Appreciate any help!
-
@Emily Zeiger What is in the [auto] column? I'm assuming that is just a system generated auto-number type column?
-
@Paul Newcome that is correct
-
@Emily Zeiger Ok. I am not sure I follow exactly what you are wanting to accomplish. Are you trying to pull the [Barcode 3]@row into the [Barcode 3 Entry] cell on the same row?
-
@Paul Newcome For each of the columns title Barcode 1 entry, Barcode 2 entry, barcode 3 entry, barcode 4 entry, barcode 5 entry; I have the same formula with a variation for that column. I am using an intake form for someone to utilize when handling boxes during a move. They will be able to use one form to scan in up to 5 identifying barcodes so that I can track the boxes during the move. However, they will be required at pickup, at drop off, at final destination placement. So there could be a series of cells that have the same barcode in it. I want only the most recent barcode to show up in cells Barcode 1 entry - Barcode 5 entry so I can pull an index match formula over to another sheet to show current status. The main issue I have is that at pickup the barcode code be scanned into barcode 1, then at drop off it could be scanned into barcode 4, then at placement it could be in barcode 2. So I'm trying to figure out how to best right the formula to look for only the most recent entry for that specific box. Hopefully that makes sense.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!