Finding the second to last in a collection
Hello -
I'm using INDEX to find the value of [Scanned Location] that corresponds to the MAX [ROW#] of a collection that matches my current [Asset ID]. My formula is =INDEX([Scanned Location]:[Scanned Location], MAX(COLLECT([ROW#]:[ROW#], [Asset ID]:[Asset ID], [Asset ID]@row))).
Now what I want to do is find the second to last [Scanned Location] value of that same collection. Any ideas on the syntax here? So, if asset 13 has a collection of locations (A, D, B, Z), it would return "B". Right now, I can isolate "A" (using INDEX, 1), and "Z", using MAX. I'd like to isolate the second to last, but I can't use 3, since that collection could be 1 or many values.
Thanks,
Mark
Best Answer
-
See my solution in this other post, you could use the same approach
Answers
-
See my solution in this other post, you could use the same approach
-
Thank you, use of helper columns here got me on the right track.
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!