nth Last Entry With A Specific Name

Jasper_

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.

Jasper_

Untitled2.png

Example of the what the sheet that I need to pull the values into will look like

Jasper_

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?

Paul Newcome

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.