Return MAX based on multiple criteria (user and date)

[B0ns41]

Hi all,

I have a table with usernames and date. I need to get the max of number of times the username appears by date. If I searh Paul the result will be 4.

Search: Paul
Result: 4

12-01-2018: 4
14-01-2018: 2
15-01-2018: 1

Name Date Paul 12-01-2018 John 12-01-2018 Paul 12-01-2018 Melissa 12-01-2018 Jesica 12-01-2018 Paul 12-01-2018 Paul 12-01-2018 Melissa 12-01-2018 Jesica 14-01-2018 Melissa 14-01-2018 Paul 14-01-2018 Jesica 14-01-2018 Paul 14-01-2018 John 14-01-2018 John 15-01-2018 Paul 15-01-2018 Melissa 16-01-2018 Melissa 17-01-2018 John 18-01-2018 <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks.

 

[skorpionkz]

Why can't you use Pivot Table?

BTW, you don't need maximum, but count for this

 

[MickG]

With the name in "D1" , try this for results (Max Count) in "E1".

[COLOR="Navy"]Sub[/COLOR] MG12Apr03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, -1).Value = Range("D1") [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Value, 1
        [COLOR="Navy"]Else[/COLOR]
            .Item(Dn.Value) = .Item(Dn.Value) + 1
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    oMax = Application.Max(oMax, .Item(K))
[COLOR="Navy"]Next[/COLOR] K
Range("E1") = oMax
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Marcelo Branco]

Hi all,

I have a table with usernames and date. I need to get the max of number of times the username appears by date. If I searh Paul the result will be 4.

I think you do want the max of number of times the username appears in any date.

If so, try
Dates as dd/mm/yyyy

	A​	B​	C​	D​	E​
1​	Name​	Date​		Name​	Max​
2​	Paul​	12/01/2018​		Paul​	4​
3​	John​	12/01/2018​
4​	Paul​	12/01/2018​
5​	Melissa​	12/01/2018​
6​	Jesica​	12/01/2018​
7​	Paul​	12/01/2018​
8​	Paul​	12/01/2018​
9​	Melissa​	12/01/2018​
10​	Jesica​	14/01/2018​
11​	Melissa​	14/01/2018​
12​	Paul​	14/01/2018​
13​	Jesica​	14/01/2018​
14​	Paul​	14/01/2018​
15​	John​	14/01/2018​
16​	John​	15/01/2018​
17​	Paul​	15/01/2018​
18​	Melissa​	16/01/2018​
19​	Melissa​	17/01/2018​
20​	John​	18/01/2018​

<tbody>
</tbody>

Array formula in E2
=MAX(FREQUENCY(IF(A$2:A$20=D2,B$2:B$20),B$2:B$20))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[B0ns41]

Why can't you use Pivot Table?

BTW, you don't need maximum, but count for this

I don't want to use Pivot Table. Yes, count will be enough.

 

[B0ns41]

With the name in "D1" , try this for results (Max Count) in "E1".

[COLOR=Navy]Sub[/COLOR] MG12Apr03
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] K [COLOR=Navy]As[/COLOR] Variant, oMax [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Dn.Offset(, -1).Value = Range("D1") [COLOR=Navy]Then[/COLOR]
        [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
            .Add Dn.Value, 1
        [COLOR=Navy]Else[/COLOR]
            .Item(Dn.Value) = .Item(Dn.Value) + 1
        [COLOR=Navy]End[/COLOR] If
   [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
    oMax = Application.Max(oMax, .Item(K))
[COLOR=Navy]Next[/COLOR] K
Range("E1") = oMax
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks but I don't want to use VBA, only formulas.

 

[B0ns41]

I think you do want the max of number of times the username appears in any date.

If so, try
Dates as dd/mm/yyyy

	A​	B​	C​	D​	E​
1​	Name​	Date​		Name​	Max​
2​	Paul​	12/01/2018​		Paul​	4​
3​	John​	12/01/2018​
4​	Paul​	12/01/2018​
5​	Melissa​	12/01/2018​
6​	Jesica​	12/01/2018​
7​	Paul​	12/01/2018​
8​	Paul​	12/01/2018​
9​	Melissa​	12/01/2018​
10​	Jesica​	14/01/2018​
11​	Melissa​	14/01/2018​
12​	Paul​	14/01/2018​
13​	Jesica​	14/01/2018​
14​	Paul​	14/01/2018​
15​	John​	14/01/2018​
16​	John​	15/01/2018​
17​	Paul​	15/01/2018​
18​	Melissa​	16/01/2018​
19​	Melissa​	17/01/2018​
20​	John​	18/01/2018​

<tbody>
</tbody>

Array formula in E2
=MAX(FREQUENCY(IF(A$2:A$20=D2,B$2:B$20),B$2:B$20))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

That's corrrect. It works, Thanks!