Excel : Get the most frequent value for each group

Question

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

At 4:35:51, two rows have Value=111 and another two have Value=132. So how do you determine 132 is "most frequent"? — HansUp, Jan 17 '16 at 20:14
If we find two or more values with same frequent we choose the highest value — Horthe92, Jan 17 '16 at 20:17
You denote the correct answer by clicking the green check mark by the answer. I have reverted it back to the original. — Scott Craner, Jan 17 '16 at 23:23
It's done ;) , I think i need to create a new separate question for SQL — Horthe92, Jan 17 '16 at 23:25

Scott Craner · Accepted Answer · 2016-01-17T23:22:27.223

1

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:

edited Jan 17 '16 at 23:22

answered Jan 17 '16 at 20:44

Scott Craner

148,073
10
49
81

1

Maybe I am misunderstanding the question, but doesn't just using `=MAX(IF($A$2:$A$11=E2,$B$2:$B$11))` give the same result? – rwilson Jan 17 '16 at 20:53
@rwilson Not quite, but your comment made my think that I was over thinking, so I simplified. the problem is that the OP wants when there is a tie that the higher value wins. – Scott Craner Jan 17 '16 at 20:56
Excelent solution my brother, Thank you so much, Now am searching for the same solution with an sql query :D – Horthe92 Jan 17 '16 at 21:08
Unfortunately @rwilson doesn't understand my question , I need to get the most occurrent value of each time, your formula return the higher value of each time, it's not my goal :) – Horthe92 Jan 17 '16 at 22:45
First answer of Scott seems ok `=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)) ` – Horthe92 Jan 17 '16 at 22:50
@Horthe92 The shortened one step will do what you want. It will return the highest if there are more than one that has the highest recurrence. I just took the two formula and combined them to do it in one step. The single formula should give the same answer as the two step. rwilson's did spark me to shorten the formula and still get the same result. If this is correct answer please mark it as such by clicking on the green check mark by the answer. – Scott Craner Jan 17 '16 at 23:05

Gordon Linoff · Answer 2 · 2016-01-17T23:09:21.417

1

Here is one way to do this in MS Access:

select tv.*
from (select time, value, count(*) as cnt
      from t 
      group by time, value
     ) as tv
where exists (select 1
              from (select top 1 time, value, count(*) as cnt
                    from t as t2
                    where t.time = t2.time
                    group by time, value
                    order by count(*) desc, value desc
                  ) as x
              where x.time = tv.time and x.value = tv.value
             );

MS Access doesn't support features such as window functions or CTEs that make this type of query easier in other databases.

edited Jan 17 '16 at 23:09

answered Jan 17 '16 at 20:49

Gordon Linoff

1,242,037
58
646
786

that would not work if two values are equally frequent at a given time right? Both would get selected – Guig Jan 17 '16 at 20:55
@Guig . . . I restructured the whole query so it will only return one value per time. – Gordon Linoff Jan 17 '16 at 23:09

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

Would that work? I haven't tried and got inspired here

;WITH t3 AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY time ORDER BY c DESC, value DESC) AS rn
   FROM (SELECT COUNT(*) AS c, time, value FROM t GROUP BY time, value) AS t2
)
SELECT *
FROM t3
WHERE rn = 1

edited May 23 '17 at 12:04

Community

1
1

answered Jan 17 '16 at 20:37

Guig

9,891
7
64
126

Does it support mysql? – Horthe92 Jan 17 '16 at 20:51
I hope so:) I can't test it a the moment – Guig Jan 17 '16 at 20:53
Ok, I'll try it , God bless you – Horthe92 Jan 17 '16 at 20:57
It might not work with mysql.. http://stackoverflow.com/questions/324935/mysql-with-clause – Guig Jan 17 '16 at 21:04
The problem is not with "WITH" clause but with ROW_NUMBER(), "WITH" can be replaced with a sub-query i guess – Horthe92 Jan 17 '16 at 21:13
Here is a way to get the row number: http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select – Guig Jan 17 '16 at 21:13

Excel : Get the most frequent value for each group

3 Answers3

Linked