Two max values in an array to show as reslut

[asad]

I got the following table

Excel Workbook
	A	B
2		SALTSIDIS hasn't paid in on more occassions than anybody else.
3
4	13/01/2011	SANTIN H
5	27/01/2011	SALTSIDIS
6	30/01/2011	SALTSIDIS
7	3/02/2011	OV
8	6/02/2011	NOVOTNY
9	10/02/2011	LAST
10	10/02/2011	SINGH R
11	10/02/2011	ARATHOON
12	18/02/2011	KEELEY
13	19/02/2011	PETERU
14	21/02/2011	OV
15	22/02/2011	FINDLAY
16	23/02/2011	SOK
17	25/02/2011	SANTIN J
18	26/02/2011	SANTIN J
19	27/02/2011	SINGH K
20	1/03/2011	SINGH R
21	1/03/2011	PETERU
22	3/03/2011	SCHULZ
23	5/03/2011	SALVATORE
24	5/03/2011	OV
25	8/03/2011	VA
26	8/03/2011	SOK
27
28	10/03/2011	LAST
29	14/03/2011	SINGH R
30	14/03/2011	VA
31	15/03/2011	SINGH R
32	17/03/2011	SCHULZ
33	20/03/2011	SALTSIDIS
34	23/03/2011	SALTSIDIS
35	24/03/2011	SINGH K
36	1/04/2011	DERUM
37	3/04/2011	SALTSIDIS
38	5/04/2011	DERUM
39	8/04/2011	PETERU
40	11/04/2011	FINDLAY
41	12/04/2011	SALTSIDIS
42	12/04/2011	SINGH K
43	14/04/2011	LAST
44	19/04/2011	VA
45	18/04/2011	SINGH K
46	19/04/2011	SINGH K
47	19/04/2011	HOLT
48	20/04/2011	FINDLAY
49	24/04/2011	SINGH K
50	26/04/2011	SINGH K
51	25/04/2011	SALVATORE
52	28/04/2011	DERUM
53	30/04/2011	SINGH K
54	5/05/2011	SINGH K
55	22/05/2011	SCHULZ
56	24/05/2011	RIVERA
57	24/05/2011	FINDLAY
58	31/05/2011	DERUM
59	31/05/2011	SANTIN H
60	1/06/2011	SALEH
61	3/06/2011	SANTIN H
62	6/06/2011	DERUM
63	7/06/2011	SANTIN H
64	7/06/2011	FINDLAY
65	9/06/2011	PETERU
66	12/06/2011	SINGH K
67	10/06/2011	DERUM
68	18/06/2011	ROFAEL
69	19/06/2011	SALTSIDIS
70	21/06/2011	SALEH
71	21/06/2011	LAST
72	24/06/2011	OV
73	25/06/2011	SALEH
74	24/06/2011	HOWARD
75	27/06/2011	SALTSIDIS
76	28/06/2011	SALTSIDIS
77	28/06/2011	SALTSIDIS
78
79
Not Paid In and Shortages

In the above, the answer should be "SINGH K, SALTSIDIS hasn't paid in on more occassions than anybody else". But it shows SALTSIDIS only. SINGH K and SALTSIDIS are both occurring 10 times. How can I fix this issue?

Thanks for your help.

Asad

 

[asad]

bump

 

[asad]

Still No Luck

 

[asad]

I am trying to use a helper cell. I am trying to get the two or more names in cells in the same row (Row#2) and then concanate them in B2. the formula I got so far is like this:

{=SMALL(INDEX($B$4:$B$501,MATCH(MAX(COUNTIF($B$4:$B$501,$B$4:$B$501)),COUNTIF($B$4:$B$501,$B$4:$B$501),0),1),COLUMNS($C2:C2))}

But it is not working. Can you guys please help me with this one?

 

[asad]

I tried this one too but no luck:

{=INDEX($B$4:$B$501,SMALL(MATCH(MAX(COUNTIF($B$4:$B$501,$B$4:$B$501)),COUNTIF($B$4:$B$501,$B$4:$B$501),0),COLUMNS($C2:C2)),1)}

This one gives me the same person as my original formula, and then gives error for the next one

Asad

 

[T. Valko]

I tried this one too but no luck:
{=INDEX($B$4:$B$501,SMALL(MATCH(MAX(COUNTIF($B$4:$B$501,$B$4:$B$501)),COUNTIF($B$4:$B$501,$B$4:$B$501),0),COLUMNS($C2:C2)),1)}

This one gives me the same person as my original formula, and then gives error for the next one

Asad

Try this...

In the formulas Rng refers to B$4:B$77

Enter this array formula** in D4. This will return the count of the name(s) that appear the most often.

=MAX(COUNTIF(Rng,Rng))

Enter this array formula** in E4. This will return the count of how many names appear the most often.

=SUM(IF(FREQUENCY(MATCH(Rng&"",Rng&"",0),ROW(Rng)-MIN(ROW(Rng))+1)=D4,1))

Enter this array formula** in F4. This will return the name that appears the most often.

=INDEX(Rng,MODE(IF(Rng<>"",MATCH(Rng,Rng,0))))

Enter this array formula** in F5. This will return any other names that also appear the max number of times.

=IF(ROWS(F$4:F5)>E$4,"",INDEX(Rng,MODE(IF(Rng<>"",IF(COUNTIF(F$4:F4,Rng)=0,MATCH(Rng,Rng,0))))))

Copy down until you get blanks.

 

[asad]

Thanks a lot Biff,

I changed your formula a little bit because of my set up and it worked perfect. Here is a post for anybody else who want to use it:

Excel Workbook
	A	B	C	D	E	F	G
2		10	2	SALTSIDIS	SINGH K
3	SALTSIDIS SINGH K Haven't paid in on more occassions than anybody else.
Not Paid In and Shortages

Thanks again

Asad

 

[T. Valko]

Try this...

In the formulas Rng refers to B$4:B$77

Enter this array formula** in D4. This will return the count of the name(s) that appear the most often.

=MAX(COUNTIF(Rng,Rng))

Enter this array formula** in E4. This will return the count of how many names appear the most often.

=SUM(IF(FREQUENCY(MATCH(Rng&"",Rng&"",0),ROW(Rng)-MIN(ROW(Rng))+1)=D4,1))

Enter this array formula** in F4. This will return the name that appears the most often.

=INDEX(Rng,MODE(IF(Rng<>"",MATCH(Rng,Rng,0))))

Enter this array formula** in F5. This will return any other names that also appear the max number of times.

=IF(ROWS(F$4:F5)>E$4,"",INDEX(Rng,MODE(IF(Rng<>"",IF(COUNTIF(F$4:F4,Rng)=0,MATCH(Rng,Rng,0))))))

Copy down until you get blanks.

I forgot to include the instructions on how to enter an array formula.

Enter this array formula**

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[T. Valko]

Thanks a lot Biff,

I changed your formula a little bit because of my set up and it worked perfect. Here is a post for anybody else who want to use it:

Excel Workbook
	A	B	C	D	E	F	G
2		10	2	SALTSIDIS	SINGH K
3	SALTSIDIS SINGH K Haven't paid in on more occassions than anybody else.
Not Paid In and Shortages

Thanks again

Asad

You're welcome. Thanks for the feedback!

 

[asad]

In the formulas Rng refers to B$4:B$77

I have been trying to change the Rng reference so that it will take into consideration only those cells in $B$4:$B$500 that match up with date in $A$4:$A$500 and the date should be more than equal to $K$2.
I tried this

=IF($A$4:$A$500 > =$K$2,$B$4:$B$500)

as a ref to name Rng in Name Manager. But it does not work. What is it that I am doing wrong?

Asad