Using MAX/IF Arrays to find multiple values

[gilligan]

I have a large spreadsheet (example below) of charitable donors dumped from a database. I'd like to find the Largest Amount and Latest Date for each donation, by Donor.

I've been trying to use MAX/IF arrays (after concatenating the first/last names) but it's not quite working.

Any help would be greatly appreciated.

<tbody>
[TD="class: xl67"]

<tbody>
[TD="class: xl65"]date_given[/TD]
[TD="class: xl67"]amt[/TD]
[TD="class: xl65, width: 85"]last_name[/TD]
[TD="class: xl65, width: 74"]first_name[/TD]
[TD="class: xl65, width: 64"] Largest_Amt[/TD]
[TD="class: xl65, width: 64"]Latest_Date[/TD]

[TD="class: xl66"]12/20/2013[/TD]
[TD="class: xl67"]1000[/TD]
[TD="class: xl65"]Rogers[/TD]
[TD="class: xl65"]Ron[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]3/17/2016[/TD]
[TD="class: xl67"]9[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65"]Jim[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]5/6/2011[/TD]
[TD="class: xl67"]1000[/TD]
[TD="class: xl65"]Rogers[/TD]
[TD="class: xl65"]Jeff[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]10/9/2007[/TD]
[TD="class: xl67"]50[/TD]
[TD="class: xl65"]Jones[/TD]
[TD="class: xl65"]Adam[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]8/15/2012[/TD]
[TD="class: xl67"]50[/TD]
[TD="class: xl65"]Abbott[/TD]
[TD="class: xl65"]Brian[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]3/12/2016[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]11/25/2014[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl65"]Arnold[/TD]
[TD="class: xl65"]Bill[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]3/19/2015[/TD]
[TD="class: xl67"]20[/TD]
[TD="class: xl65"]Smith[/TD]
[TD="class: xl65"]John[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]1/16/2009[/TD]
[TD="class: xl67"]15000[/TD]
[TD="class: xl65"] Washington[/TD]
[TD="class: xl65"]Albert[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]1/22/2008[/TD]
[TD="class: xl67"]2000[/TD]
[TD="class: xl65"]Rogers[/TD]
[TD="class: xl65"]Jeff[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]9/4/2013[/TD]
[TD="class: xl67"]100[/TD]
[TD="class: xl65"]Abrams[/TD]
[TD="class: xl65"]Dan[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]2/22/2007[/TD]
[TD="class: xl67"]1000[/TD]
[TD="class: xl65"]Rogers[/TD]
[TD="class: xl65"]Jeff[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]12/4/2014[/TD]
[TD="class: xl67"]270[/TD]
[TD="class: xl65"]Evans[/TD]
[TD="class: xl65"]Joyce[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]3/18/2013[/TD]
[TD="class: xl67"]250[/TD]
[TD="class: xl65"]Reynolds[/TD]
[TD="class: xl65"]Evan[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]12/31/2010[/TD]
[TD="class: xl67"]150[/TD]
[TD="class: xl65"]Reynolds[/TD]
[TD="class: xl65"]William[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

[TD="class: xl66"]10/5/2011[/TD]
[TD="class: xl67"]2500[/TD]
[TD="class: xl65"]Rogers[/TD]
[TD="class: xl65"]Ron[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]

</tbody>

[/TD]

</tbody>

 

[admiral100]

Hi,

If I understood correctly..
It's an array formula so Ctrl+Shift+Enter NOT just Enter

For Largest_Amt

=INDEX($B$2:$B$17,MATCH(1,IF($C$2:$C$17&$D$2:$D$17=C2&D2,IF($B$2:$B$17=MAX(IF($C$2:$C$17&$D$2:$D$17=C2&D2,$B$2:$B$17)),0))))

For Latest_Date

=TEXT(INDEX($A$2:$A$17,MATCH(1,IF($C$2:$C$17&$D$2:$D$17=C2&D2,IF($A$2:$A$17=MAX(IF($C$2:$C$17&$D$2:$D$17=C2&D2,$A$2:$A$17)),0)))),"DD/MM/YYYY")

 

[AhoyNC]

Try:

[TABLE="class: grid, width: 460"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: #9BC2E6"]Row/Col
[/TD]
[TD="class: xl67, width: 89, bgcolor: #9BC2E6"]A
[/TD]
[TD="class: xl67, width: 77, bgcolor: #9BC2E6"]B
[/TD]
[TD="class: xl67, width: 104, bgcolor: #9BC2E6"]C
[/TD]
[TD="class: xl67, width: 82, bgcolor: #9BC2E6"]D
[/TD]
[TD="class: xl67, width: 99, bgcolor: #9BC2E6"]E
[/TD]
[TD="class: xl67, width: 97, bgcolor: #9BC2E6"]F
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]1
[/TD]
[TD="class: xl66, bgcolor: #D9D9D9"]date_given
[/TD]
[TD="class: xl66, bgcolor: #D9D9D9"]amt
[/TD]
[TD="class: xl66, bgcolor: #D9D9D9"]last_name
[/TD]
[TD="class: xl66, bgcolor: #D9D9D9"]first_name
[/TD]
[TD="class: xl66, bgcolor: #D9D9D9"]Largest_Amt
[/TD]
[TD="class: xl66, bgcolor: #D9D9D9"]Latest_Date
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/20/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1000
[/TD]
[TD="class: xl64, bgcolor: transparent"]Rogers
[/TD]
[TD="class: xl64, bgcolor: transparent"]Ron
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2500
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/20/2013
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/17/2016
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl64, bgcolor: transparent"]Smith
[/TD]
[TD="class: xl64, bgcolor: transparent"]Jim
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/17/2016
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5/6/2011
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1000
[/TD]
[TD="class: xl64, bgcolor: transparent"]Rogers
[/TD]
[TD="class: xl64, bgcolor: transparent"]Jeff
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5/6/2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10/9/2007
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl64, bgcolor: transparent"]Jones
[/TD]
[TD="class: xl64, bgcolor: transparent"]Adam
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10/9/2007
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8/15/2012
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl64, bgcolor: transparent"]Abbott
[/TD]
[TD="class: xl64, bgcolor: transparent"]Brian
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8/15/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/12/2016
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl64, bgcolor: transparent"]Smith
[/TD]
[TD="class: xl64, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/12/2016
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11/25/2014
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl64, bgcolor: transparent"]Arnold
[/TD]
[TD="class: xl64, bgcolor: transparent"]Bill
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11/25/2014
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/19/2015
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl64, bgcolor: transparent"]Smith
[/TD]
[TD="class: xl64, bgcolor: transparent"]John
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/12/2016
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1/16/2009
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15000
[/TD]
[TD="class: xl64, bgcolor: transparent"]Washington
[/TD]
[TD="class: xl64, bgcolor: transparent"]Albert
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1/16/2009
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1/22/2008
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2000
[/TD]
[TD="class: xl64, bgcolor: transparent"]Rogers
[/TD]
[TD="class: xl64, bgcolor: transparent"]Jeff
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5/6/2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9/4/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl64, bgcolor: transparent"]Abrams
[/TD]
[TD="class: xl64, bgcolor: transparent"]Dan
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9/4/2013
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]13
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2/22/2007
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1000
[/TD]
[TD="class: xl64, bgcolor: transparent"]Rogers
[/TD]
[TD="class: xl64, bgcolor: transparent"]Jeff
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2000
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5/6/2011
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/4/2014
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]270
[/TD]
[TD="class: xl64, bgcolor: transparent"]Evans
[/TD]
[TD="class: xl64, bgcolor: transparent"]Joyce
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]270
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/4/2014
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]15
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/18/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]250
[/TD]
[TD="class: xl64, bgcolor: transparent"]Reynolds
[/TD]
[TD="class: xl64, bgcolor: transparent"]Evan
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]250
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3/18/2013
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]16
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/31/2010
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]150
[/TD]
[TD="class: xl64, bgcolor: transparent"]Reynolds
[/TD]
[TD="class: xl64, bgcolor: transparent"]William
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]150
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/31/2010
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #9BC2E6"]17
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10/5/2011
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2500
[/TD]
[TD="class: xl64, bgcolor: transparent"]Rogers
[/TD]
[TD="class: xl64, bgcolor: transparent"]Ron
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]2500
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12/20/2013
[/TD]
[/TR]
</tbody>[/TABLE]


These are array formulas and must be entered with CTRL-SHIFT-ENTER.
Formula in E2
=MAX(IF(C2&"/"&D2=$C$2:$C$17&"/"&$D$2:$D$17,$B$2:$B$17))

Formula in F2
=MAX(IF(C2&"/"&D2=$C$2:$C$17&"/"&$D$2:$D$17,$A$2:$A$17))

 

[gilligan]

Heh, I see why I wasn't getting it.

Ok, so two different ways to get to the same place. Is there an advantage to using the Index function vs MAX or vice versa?

 

[Aladin Akyurek]

Heh, I see why I wasn't getting it.

Ok, so two different ways to get to the same place. Is there an advantage to using the Index function vs MAX or vice versa?

Shouldn't the latest date be the date of the donor's largest amount?

 

[gilligan]

Shouldn't the latest date be the date of the donor's largest amount?

Good question, but no, I guess they're using that column to target donors who haven't given in X amount of time, regardless of the amount. But thanks for pointing that out, it would have been critical if it really was supposed to be as you thought.

 

[Aladin Akyurek]

Good question, but no, I guess they're using that column to target donors who haven't given in X amount of time, regardless of the amount. But thanks for pointing that out, it would have been critical if it really was supposed to be as you thought.

If the largest amount of donation and the latest date on which a donation is done are what is required, my question becomes irrelevant indeed.

 

[gilligan]

Just wanted to jump back in here and give a great big THANK YOU for everyone's help. I implemented the MAX solution and gave it to my friend who works at the charity and they're thrilled at the amount of time they'll save.

 

[admiral100]

You are welcome , thanks for sharing

 

[AhoyNC]

You're welcome. Glad we could help.
I know you asked about the advantage of one formula over the other. My guess is that the MAX formula would be more efficient since it is calling less functions.
Thanks for the feedback.