nth Last Entry With A Specific Name

So basically I need to find the last, 2nd last, 3rd last, 4th last, 5th last, 6th last and 7th last entries in a sheet that have a specific name. Each of the nth entries will go in a separate column on a new sheet. I was thinking about using INDEX but am unsure how to find the nth row number the name is = to the one I'm looking for. New entries are being put into the sheet about 100 times a day but normally not more than once per name.
Answers
-
Example of the what the sheet that I need to pull the values into will look like
-
Damn I'm I feel so close but so far. So I made a helper column with the idea that I can count the number of times this Name has appeared in the sheet before this entry;
=COUNTIF([ItemName]1:[ItemName]@row, =[ItemName]@row)
But I can't make it a column formula because the range needs to be only entries above this one, any fix?
-
Insert an auto-number column with no special formatting. From there you can use an INDEX/COLLECT with a LARGE/COLLECT in the criteria.
=INDEX(COLLECT({Column To Pull Data From}, {Auto-Number Column}, @cell = LARGE(COLLECT({Auto-Number Column}, {Item Name Column}, @cell = ItemName@row), #)), 1)
The bold # is where you would put in 1, 2, 3, 4, 5, 6, 7, etc. for the "nth" requirement.
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!