Finding top 5-10 samples checked out within a date range (ability to change the date range)

Question

I am working on a spreadsheet with a “recap” sheet to display the top 5-10 samples that have been checked out. I have been able to use the Large function to setup a top 5-10 successfully. I am now having issues with being able to add the date range that needs to be able to be adjusted. This is a large set of data. The other spreadsheet with the samples only list the date (top row) and the name of the sample (columns). When I have tried to use Large and IF/IFS together, I either get a NUM error or when I adjust the date range the numbers on my recap sheet do not accurately reflect the top 5 within the given date range. Any help is much appreciated!

-Taylor

jkpieterse · Answer

What does your data look like?Have you tried using a pivot table? That has a top n option.

taylor.easley2015 · Answer

Hi Jan,&nbsp;Here is a sample of the data. I have tried a pivot table and did not have any luck. I could be doing it incorrectly though.&nbsp;-Taylor

jkpieterse · Answer

I used PowerQuery (Datatab of ribbon, "From table" button) to create the table on the second worksheet (see attached).

taylor easley · Answer

Hi Jan,&nbsp;Thank you. I am having issues with using the date range and being able to adjust the date range. The pivot table shows the highest 5 numbers within the entire spreadsheet rather than during the date range (7/23/2018 - 7/30/2018). Do you know how to include this in the formula or pivot table?&nbsp;Thank you,&nbsp;Taylor

Forum Discussion

Finding top 5-10 samples checked out within a date range (ability to change the date range)

Share

Resources