Populate Name Based On Highest Amount and Date

[kumara_faith]

Hi,

I have the following set of data:

Excel Workbook
	B	C	D
3	Date	Amount	Name
4	01-01-15	250	Lev
5	05-04-15	3665	John
6	08-07-15	587	Suzanne
7	09-05-15	450	Jane
8	21-10-16	120	John
9	21-10-16	1350	John
10	21-10-16	250	Keith
11	21-10-16	1470	May
12	21-10-16	258	Suzanne
13	21-10-16	569	Suzanne
14	22-10-16	458	Lev
15	22-10-16	874	John
16	22-10-16	158	Kerry
17	22-10-16	963	Kerry
18	30-10-16	100	May
19	30-10-16	254	Suzanne
20	30-10-16	125	Jane
21	30-10-16	365	Lev
22	30-10-16	785	Lev
Sheet2

I need to populate the following:

a) Person with highest amount for the latest date
b) Person with the highest amount by each month
c) Person with the highest amount for YTD 2016 and YTD 2015

I managed to get the highest amount for the latest date with the following formula but beyond that I am stuck.Appreciate all the help.

{=MAX(IF(Sheet2!B4:B22=D3,Sheet2!C4:C22))}

 

[kumara_faith]

Hi,

Appreciate if anyone could help. Thanks in advance for your patience and time.

 

[Aladin Akyurek]

That is a tall order...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​						5​
2​					max amt latest date	month	name(s)
3​	Date	Amount	Name		10/30/2016​	1/1/2015​	Lev
4​	1/1/2015​	250​	Lev​		785​	4/1/2015​	John
5​	4/5/2015​	3665​	John​		name(s)	5/1/2015​	Jane
6​	7/8/2015​	587​	Suzanne​		Linda	7/1/2015​	Suzanne
7​	5/9/2015​	450​	Jane​		Lev	10/1/2016​	Rob	May
8​	10/20/2016​	1470​	Rob​
9​	10/21/2016​	120​	John​
10​	10/21/2016​	1350​	John​
11​	10/21/2016​	250​	Keith​
12​	10/21/2016​	1470​	May​
13​	10/21/2016​	258​	Suzanne​
14​	10/21/2016​	569​	Suzanne​
15​	10/22/2016​	458​	Lev​
16​	10/22/2016​	874​	John​
17​	10/22/2016​	158​	Kerry​
18​	10/22/2016​	963​	Kerry​
19​	10/30/2016​	100​	May​
20​	10/30/2016​	254​	Suzanne​
21​	10/30/2016​	125​	Jane​
22​	10/30/2016​	785​	Linda​
23​	10/30/2016​	365​	Lev​
24​	10/30/2016​	785​	Lev​

<tbody>
</tbody>

(a) In E3 just enter:

=MAX($A$4:$A$24)

In E4 control+shift+enter, not just enter:

=MAX(IF($A$4:$A$24=E3,$B$4:$B$24))

In E6 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$4:$C$24,SMALL(IF($B$4:$B$24=E$4,ROW($C$4:$C$24)-ROW($C$4)+1),ROWS($E$6:E6))),"")

(b) In F1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER($A$4:$A$24),$A$4:$A$24-DAY($A$4:$A$24)+1),$A$4:$A$24-DAY($A$4:$A$24)+1),1))

In F3 control+shift+enter and copy down:

=IF(ROWS(F$3:F3)>$F$1,"",MIN(IF(ISNA(MATCH($A$4:$A$24-DAY($A$4:$A$24)+1,$F$2:F2,0)),$A$4:$A$24-DAY($A$4:$A$24)+1)))

In G3 control+shift+enter, copy across, and down:

=IFERROR(INDEX($C$4:$C$24,SMALL(IF($A$4:$A$24-DAY($A$4:$A$24)+1=$F3,IF($B$4:$B$24=MAX(IF($A$4:$A$24-DAY($A$4:$A$24)+1=$F3,$B$4:$B$24)),ROW($C$4:$C$24)-ROW($C$4)+1)),COLUMNS($G3:G3))),"")

(c) YTD names I leave to you...

 

[kumara_faith]

Hi Aladin,

Thank you so much for your time and patience. I used the formula but I am getting as follows for (b):

Excel Workbook
	A	B	C	D	E	F	G
2	*	*	*	*	max amt latest date	month	name(s)
3	Date	Amount	Name	*	30-10-16	1	0
4	01-01-15	250	Lev	*	785	*	*
5	05-04-15	3665	John	*	*	*	*
6	08-07-15	587	Suzanne	*	Lev	*	*
7	09-05-15	450	Jane	*	*	*	*
8	21-10-16	120	John	*	*	*	*
9	21-10-16	1350	John	*	*	*	*
10	21-10-16	250	Keith	*	*	*	*
11	21-10-16	1470	May	*	*	*	*
12	21-10-16	258	Suzanne	*	*	*	*
13	21-10-16	569	Suzanne	*	*	*	*
14	22-10-16	458	Lev	*	*	*	*
15	22-10-16	874	John	*	*	*	*
16	22-10-16	158	Kerry	*	*	*	*
17	22-10-16	963	Kerry	*	*	*	*
18	30-10-16	100	May	*	*	*	*
19	30-10-16	254	Suzanne	*	*	*	*
20	30-10-16	125	Jane	*	*	*	*
21	30-10-16	365	Lev	*	*	*	*
22	30-10-16	785	Lev	*	*	*	*
Sheet2

For (c), is there suppose to be a formula in cell H7?

 

[Aladin Akyurek]

See the workbook that implements the set up described in post #3.