nth Last Entry With A Specific Name

Jasper_
Jasper_ ✭✭
edited 04/01/22 in Formulas and Functions

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

  • Jasper_
    Jasper_ ✭✭

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

  • Jasper_
    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
    Paul Newcome Community Champion

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!