Hi All, I've been a long time user/reader and fan of all your advice, from which I have solved many queries to build the spreadsheet I am about to seek help on. The answer should be a lot simpler to you, as I feel I have over complicated it in my head as I'm over familiar with the sheet. But I feel the answer lies with a formula that I haven't used all that much. somewhere within the (Large, Max, If, CountIF).
Firstly please let me explain the workings of this sheet;
It was developed to count certain areas/zones at certain times throughout the day. It started as a basic log of numbers then I was tasked with producing easily readable reports, however by this time we were that far down the line that changing the input method/Interface wasn't possible without making it unrecognizable from its current form. So i was left with a VERY convoluted way of extracting the data into a "Pivot Master" data set to be able to produce the aforementioned report (which was done with great success - with many thanks to this forum and never having to actually ask, but gather ideas and solve questions using the depth of info contained within this forum).
My current issue is that I need to draw in the top 5 values across a week, as well as the day and time these occurred appearing in the adjacent cells (I have done this), BUT there will be duplicate entries on many occasions as there are 111 cells to create the top 5 from. I also need to replicate this for every week from the beginning of the year and going forward for the foreseeable future so needs to be easy to either drag and fill or copy and paste the table.
My current effort looks like this table below, currently E3 and E4 return 99 (there are 2 99's in the data set so this is correct) but the day and time for both in column C & D return the same (Sunday 16:30) but one should return a different value (Sunday 18:30) what can I add to make excel recognize this duplicate?
<tbody>
</tbody>
Am I looking at applying a condition to the formulas in column C & D as column E shows the right data?
The sheet 'Pivot Master'
Column B contains Day names. D contains Time starting at 06:30 increasing by 1 hour every row.
I tried to get screengrabs in here to make it easier to see what is happening on the other sheets but i'm awful at forums!
Thanks in advance for any replies/help I can get on this.
Firstly please let me explain the workings of this sheet;
It was developed to count certain areas/zones at certain times throughout the day. It started as a basic log of numbers then I was tasked with producing easily readable reports, however by this time we were that far down the line that changing the input method/Interface wasn't possible without making it unrecognizable from its current form. So i was left with a VERY convoluted way of extracting the data into a "Pivot Master" data set to be able to produce the aforementioned report (which was done with great success - with many thanks to this forum and never having to actually ask, but gather ideas and solve questions using the depth of info contained within this forum).
My current issue is that I need to draw in the top 5 values across a week, as well as the day and time these occurred appearing in the adjacent cells (I have done this), BUT there will be duplicate entries on many occasions as there are 111 cells to create the top 5 from. I also need to replicate this for every week from the beginning of the year and going forward for the foreseeable future so needs to be easy to either drag and fill or copy and paste the table.
My current effort looks like this table below, currently E3 and E4 return 99 (there are 2 99's in the data set so this is correct) but the day and time for both in column C & D return the same (Sunday 16:30) but one should return a different value (Sunday 18:30) what can I add to make excel recognize this duplicate?
| Sheet (name) | Top values | Day | Time | Count |
| Wk 1.1.18 | 1 | =INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B2),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B2),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B2) |
| Wk 1.1.18 | 2 | =INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B3),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B3),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B3) |
| Wk 1.1.18 | 3 | =INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B4),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B4),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B4) |
| Wk 1.1.18 | 4 | =INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B5),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B5),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B5) |
| Wk 1.1.18 | 5 | =INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B6),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B6),'PIVOT MASTER'!AM$2778:AM$2888,0)) | =LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B6) |
<tbody>
</tbody>
Am I looking at applying a condition to the formulas in column C & D as column E shows the right data?
The sheet 'Pivot Master'
Column B contains Day names. D contains Time starting at 06:30 increasing by 1 hour every row.
I tried to get screengrabs in here to make it easier to see what is happening on the other sheets but i'm awful at forums!
Thanks in advance for any replies/help I can get on this.
