Search code examples
excelms-accessdata-processing

Excel : Get the most frequent value for each group

I Have a table ( excel ) with two columns ( Time 'hh:mm:ss' , Value ) and i want to get most frequent value for each group of row.

for example i have 

Time    | Value
4:35:49 | 122
4:35:49 | 122
4:35:50 | 121
4:35:50 | 121
4:35:50 | 111
4:35:51 | 122
4:35:51 | 111
4:35:51 | 111
4:35:51 | 132
4:35:51 | 132

And i want to get most frequent value of each Time

Time    | Value
4:35:49 | 122
4:35:50 | 121
4:35:51 | 132

Thanks in advance

UPDATE The first answer of @scott with helper column is the correct one

See the pic

Solution

  • You could use a helper column:

    First it will need a helper column so in C I put

    =COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2)

    Then in F2 I put the following Array Formula:

    =INDEX($B$2:$B$11,MATCH(MAX(IF($A$2:$A$11=E2,IF($C$2:$C$11 = MAX(IF($A$2:$A$11=E2,$C$2:$C$11)),$B$2:$B$11))),$B$2:$B$11,0))

    It is an array formula and must be confirmed with Ctrl-Shift-Enter. Then copied down.

    I set it up like this:

    enter image description here