Grouping by date and then top 4

[ken1921]

Hi

I have a spreadsheet with 3 columns

Date
Code
Total

There are multiple rows for each date - average 100 per date.

I want to achieve the following

1. Group by date
2. Show the top 4 records for each date based on the Total column.

All help grateful received

 

[oldbrewer]

[TABLE="width: 852"]
<colgroup><col><col span="4"><col><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 4"]I have a spreadsheet with 3 columns[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]There are multiple rows for each date - average 100 per date.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]I want to achieve the following[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]1. Group by date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]2. Show the top 4 records for each date based on the Total column.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col J[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]original data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]easy to get to here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]date[/TD]
[TD]code[/TD]
[TD]total[/TD]
[TD][/TD]
[TD][/TD]
[TD]date[/TD]
[TD]code[/TD]
[TD]total[/TD]
[TD]datecode[/TD]
[TD]helper[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]
[TD]d1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD]d1[/TD]
[TD="align: right"]2[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD]d1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]24[/TD]
[TD]d1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[TD]d1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD]d1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD]d1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]34[/TD]
[TD]d2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31[/TD]
[TD]d2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28[/TD]
[TD]d2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD]d2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD]d2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD]d2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]16[/TD]
[TD]d2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]35[/TD]
[TD]d3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]32[/TD]
[TD]d3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]29[/TD]
[TD]d3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]26[/TD]
[TD]d3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]23[/TD]
[TD]d3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD]d3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]17[/TD]
[TD]d3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD="colspan: 5"]=IF(F19<>F18,1,IF(J18=0,0,IF(J18<4,J18+1,0)))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[piotrekp1]

You might also wanna be interested in really grouping, so that you don't have duplicates, to do that you can use formula like in here:
https://www.mrexcel.com/forum/<wbr>excel-questions/460154-<wbr>formula-extract-unique-values-<wbr>array.html
or the advanced filtering (which is better).
Doing that you'll have the following result:

formulas:
F2: =MAX(IF($A$2:$A$10=E2,$C$2:$C$9))
G2: =MAX(IF($C$2:$C$10<$F2,IF($A$2:$A$10=E2,$C$2:$C$10)))
H2: =MAX(IF($C$2:$C$10<$G2, IF($A$2:$A$10=E2,IF($C$2:$C$10<$F2,$C$2:$C$10))))

Recall that these are ARRAY FORMULAS so you have to enter them by ctrl+shift+enter

they are simpler to understand if you need just 4 of the best records. You can afterwards get the position of the row using the MATCH function, and get the whole row with it.

Cheers