Top 5 list of values omitting duplicates drawn from complex workbook (Large, max, count, if)?

vhookup

New Member
Joined
Mar 6, 2018
Messages
19
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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet (name)[/TD]
[TD]Top values[/TD]
[TD]Day[/TD]
[TD]Time[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]Wk 1.1.18[/TD]
[TD]1[/TD]
[TD]=INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B2),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B2),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B2)[/TD]
[/TR]
[TR]
[TD]Wk 1.1.18[/TD]
[TD]2[/TD]
[TD]=INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B3),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B3),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B3)[/TD]
[/TR]
[TR]
[TD]Wk 1.1.18[/TD]
[TD]3[/TD]
[TD]=INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B4),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B4),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B4)[/TD]
[/TR]
[TR]
[TD]Wk 1.1.18[/TD]
[TD]4[/TD]
[TD]=INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B5),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B5),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B5)[/TD]
[/TR]
[TR]
[TD]Wk 1.1.18[/TD]
[TD]5[/TD]
[TD]=INDEX('PIVOT MASTER'!B$2778:B$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B6),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=INDEX('PIVOT MASTER'!D$2778:D$2888,MATCH(LARGE('PIVOT MASTER'!AM$2778:AM$2888,B6),'PIVOT MASTER'!AM$2778:AM$2888,0))[/TD]
[TD]=LARGE('PIVOT MASTER'!$AM$2778:$AM$2888,B6)[/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I'm also thinking now along with Large function using Small function, this seems to crop up in a few searches I've discovered. I've never used this function before so could do with some guidance of implementation.
 
Upvote 0

Forum statistics

Threads
1,223,322
Messages
6,171,448
Members
452,404
Latest member
vivek562

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top