calling top five most recent items and their associated information

Hello -

After struggling with this issue I decided to register here and see if anyone can help me. I would really, really appreciate it! My base sheet is a large table of information that is constantly being added to. In sheet #2 I have sorted the information by ID #, which correlates to date, in descending order (ie. most recent at the top of the sheet). I've done this because I needed to sort other categories by most recent, something I already figured out. THE ISSUE: On sheet #3, I want to pull the top five most recent entries (the same dates with different info are okay) for both the categories "Chasis" and "NVH" from sheet #2, along with the information associated with them in the other columns of the table on sheet #2. I've attached an abbreviated version of what my workbook looks like. Sheet #1 doesn't really matter so I've just included sheet #2 and #3. The summary sheet shows what I want to happen based on the table in sheet #2.


sheet #2

id date category location PN SN description
4/7/2018 Chasis interior numbers words or numbers words
4/15/2018 tire front right numbers words or numbers words
4/15/2018 NVH interior numbers words or numbers words
3/12/2018 NVH rear right numbers words or numbers words
3/12/2018 Chasis general numbers words or numbers words
3/1/2018 Chasis front numbers words or numbers words
3/1/2018 Chasis front left numbers words or numbers words
3/1/2018 wheel front numbers words or numbers words
2/28/2018 NVH general numbers words or numbers words
2/28/2018 NVH front numbers words or numbers words
2/27/2018 Chasis front left numbers words or numbers words
2/27/2018 mount rear left numbers words or numbers words
2/20/2018 NVH trunk numbers words or numbers words
2/15/2018 tire front right numbers words or numbers words


sheet #3

date category ranking by date location PN SN Description
4/7/2018 Chasis 1 interior numbers words or numbers words
3/12/2018 Chasis 2 general numbers words or numbers words
3/1/2018 Chasis 3 front numbers words or numbers words
3/1/2018 Chasis 4 front left numbers words or numbers words
2/27/2018 Chasis 5 front left numbers words or numbers words
4/15/2018 NVH 1 interior numbers words or numbers words
3/12/2018 NVH 2 rear right numbers words or numbers words
2/28/2018 NVH 3 general numbers words or numbers words
2/28/2018 NVH 4 front numbers words or numbers words
2/20/2018 NVH 5 trunk numbers words or numbers words


Give it a try and let me know!


- Emma

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Attached I have a work book with sheet #2 (the table of sorted data by date), and sheet #3, the desired outcome with the function I cannot figure out!

I attached a mock up workbook!

Thanks for your reply.

is that what you want?

done with PowerQuery aka Get&Transform

The second table you have created looks like what I am going for. The "Rank" column is not important. I just want to show the top five most recent in both of those categories. However, I want to find a series of formulas that can create this table, no matter the changes or additions to the table on sheet #2. Do you have any ideas for the formulas?

Thank you!

Originally Posted by emmarosz

Do you have any ideas for the formulas?

Sorry but no