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]

??Help??

 

[T. Valko]

??Help??

What date is in K2?

Tell us what result you expect.

I'm getting ready to call it a day so I won't be able to work on this until some time tomorrow.

 

[asad]

Thanks for coming to my aid Biff.
Date in k2 can be anything entered by a user, like 1/12/2011.
The original formula given by you required me to define the range Rng as $b$4:$b$500. B column has names of people. Column A has dates in it. If A55 has the same date as in k2 then I want Rng to be $b$55:$b500.
I will try tp post in some data tomorrow when I go to work.
Asad

 

[T. Valko]

Thanks for coming to my aid Biff.
Date in k2 can be anything entered by a user, like 1/12/2011.
The original formula given by you required me to define the range Rng as $b$4:$b$500. B column has names of people. Column A has dates in it. If A55 has the same date as in k2 then I want Rng to be $b$55:$b500.
I will try tp post in some data tomorrow when I go to work.
Asad

Ok, I think I understand what you want to do.

Will the date that is entered in K2 appear only once in the data range?

 

[asad]

Ok, I think I understand what you want to do.

Will the date that is entered in K2 appear only once in the data range?

The date in K2 may or may not appear more than once in column A. But, if it does then it definitely will be in consecutive/successive rows. e.g. if k2 has 1/1/2011, it may appear in A56, A57 and A58 or if it is only once then it could be just in A80.
If it is appearing more than once, I want the range from the first row that it is appearing in. In the above example, it would be A56 as the other rows (A57, and A58) are after A56. So the range would be $A$56:$A$500.

Thanks for your patience Biff.

Asad

 

[T. Valko]

The date in K2 may or may not appear more than once in column A. But, if it does then it definitely will be in consecutive/successive rows. e.g. if k2 has 1/1/2011, it may appear in A56, A57 and A58 or if it is only once then it could be just in A80.
If it is appearing more than once, I want the range from the first row that it is appearing in. In the above example, it would be A56 as the other rows (A57, and A58) are after A56. So the range would be $A$56:$A$500.

Thanks for your patience Biff.

Asad

Ok, to define the range use:

$B$500:INDEX($B$4:$B$500,MATCH($K$2,$A$4:$A$500,0))

If the date in K2 doesn't exist in A4:A500 then you'll get an error.

 

[asad]

I got the error when i inserted the formula to define the name range. I inserted it as

$B$5:INDEX($B$5:$B$500,MATCH($K$2,$A$5:$A$500,0))

here is the post

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Driver	Amount		Date Paid In	For date	Driver	Amount
2		1	2		#N/A	#N/A				Start	27/01/2011
3	#N/A									End	20/01/2012
4			$ 5.00
5	13/01/2011	SANTIN H	$ 6.20		8/06/2011	13/01/2011	SANTIN H	$ 6.20		4
6	27/01/2011	SALTSIDIS	$ 33.50		31/01/2011	27/01/2011	SALTSIDIS	$ 33.50		24
7	30/01/2011	SALTSIDIS	$ 43.70		2/02/2011	30/01/2011	SALTSIDIS	$ 43.70		24
8	3/02/2011	OV	$ 30.50		9/02/2011	3/02/2011	OV	$ 30.50		14
9	6/02/2011	NOVOTNY	$ 45.40		9/02/2011	6/02/2011	NOVOTNY	$ 45.40		1
10	10/02/2011	LAST	$ 67.10		14/02/2011	10/02/2011	LAST	$ 67.10		5
Not Paid In and Shortages

And here is the post with original formula and Rng as

='Not Paid In and Shortages'!$B$5:$B$500

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Driver	Amount		Date Paid In	For date	Driver	Amount
2		24	1		SALTSIDIS					Start	27/01/2011
3	SALTSIDIS Hasn't paid in on more occassions than anybody else.									End	20/01/2012
4			$ 5.00
5	13/01/2011	SANTIN H	$ 6.20		8/06/2011	13/01/2011	SANTIN H	$ 6.20		4
6	27/01/2011	SALTSIDIS	$ 33.50		31/01/2011	27/01/2011	SALTSIDIS	$ 33.50		24
7	30/01/2011	SALTSIDIS	$ 43.70		2/02/2011	30/01/2011	SALTSIDIS	$ 43.70		24
8	3/02/2011	OV	$ 30.50		9/02/2011	3/02/2011	OV	$ 30.50		14
9	6/02/2011	NOVOTNY	$ 45.40		9/02/2011	6/02/2011	NOVOTNY	$ 45.40		1
10	10/02/2011	LAST	$ 67.10		14/02/2011	10/02/2011	LAST	$ 67.10		5
Not Paid In and Shortages

Why is that? May be I done something wrong.

Asad

 

[asad]

Got it now .

It should have been

=INDEX('Not Paid In and Shortages'!$B$5:$B$500,MATCH('Not Paid In and Shortages'!$K$2,'Not Paid In and Shortages'!$A$5:$A$500,0)):'Not Paid In and Shortages'!$B$500

I entered it wrong. Thanks a lot for your help Biff.

Asad

 

[T. Valko]

Got it now .

It should have been

I entered it wrong. Thanks a lot for your help Biff.

Asad

Good deal. Thanks for the feedback!

 

[asad]

I also used this

=INDEX('Not Paid In and Shortages'!$B$5:$B$500,MATCH(LOOKUP('Not Paid In and Shortages'!$K$2,'Not Paid In and Shortages'!$A$5:$A$500),'Not Paid In and Shortages'!$A$5:$A$500,0)):'Not Paid In and Shortages'!$B$500

to avoid getting error if the date is not in the range.
It still is your formula but with a slight variation.

I learn everyday from this forum - Thanks to you people.

Asad