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...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]max amt latest date[/TD]
[TD]month[/TD]
[TD]name(s)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD]

10/30/2016​

[/TD]
[TD]

1/1/2015​

[/TD]
[TD]Lev[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1/1/2015​

[/TD]
[TD]

250​

[/TD]
[TD]

Lev​

[/TD]
[TD][/TD]
[TD]

785​

[/TD]
[TD]

4/1/2015​

[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

4/5/2015​

[/TD]
[TD]

3665​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD]name(s)[/TD]
[TD]

5/1/2015​

[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

7/8/2015​

[/TD]
[TD]

587​

[/TD]
[TD]

Suzanne​

[/TD]
[TD][/TD]
[TD]Linda[/TD]
[TD]

7/1/2015​

[/TD]
[TD]Suzanne[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

5/9/2015​

[/TD]
[TD]

450​

[/TD]
[TD]

Jane​

[/TD]
[TD][/TD]
[TD]Lev[/TD]
[TD]

10/1/2016​

[/TD]
[TD]Rob[/TD]
[TD]May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

10/20/2016​

[/TD]
[TD]

1470​

[/TD]
[TD]

Rob​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

10/21/2016​

[/TD]
[TD]

120​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

10/21/2016​

[/TD]
[TD]

1350​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

10/21/2016​

[/TD]
[TD]

250​

[/TD]
[TD]

Keith​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

10/21/2016​

[/TD]
[TD]

1470​

[/TD]
[TD]

May​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

10/21/2016​

[/TD]
[TD]

258​

[/TD]
[TD]

Suzanne​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

10/21/2016​

[/TD]
[TD]

569​

[/TD]
[TD]

Suzanne​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]

10/22/2016​

[/TD]
[TD]

458​

[/TD]
[TD]

Lev​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]

10/22/2016​

[/TD]
[TD]

874​

[/TD]
[TD]

John​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]

10/22/2016​

[/TD]
[TD]

158​

[/TD]
[TD]

Kerry​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]

10/22/2016​

[/TD]
[TD]

963​

[/TD]
[TD]

Kerry​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]

10/30/2016​

[/TD]
[TD]

100​

[/TD]
[TD]

May​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]

10/30/2016​

[/TD]
[TD]

254​

[/TD]
[TD]

Suzanne​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]

10/30/2016​

[/TD]
[TD]

125​

[/TD]
[TD]

Jane​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]

10/30/2016​

[/TD]
[TD]

785​

[/TD]
[TD]

Linda​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]

10/30/2016​

[/TD]
[TD]

365​

[/TD]
[TD]

Lev​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]

10/30/2016​

[/TD]
[TD]

785​

[/TD]
[TD]

Lev​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


(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.