Formula Help!?!?

[TaMiller]

I need help with a formula...I have a spreadsheet with multiple duplicates in column A. For each instance there is a duplicate in column A (account number) I need the MAX number from column O (a dollar amount).
Using MAX in the Subtotal Tool under Data makes the spreadsheet too hard to manipulate further. Any suggestions? Thanks!!

 

[Aladin Akyurek]

=MAX(IF(AccountsRange=Account,DollarRange))

which needs to be confirmed with control+shift+enter, not just with enter.

 

[TaMiller]

Thanks so much for your help but I think I should have been more specific. Will I have to specify which account number I'm lookin for? I really need to display one instance of each duplicated account number as well as the maximum dollar amount in any row in column O that contains that account number. For example:

Acct# Dollar Amt
123 $100
123 $200
456 $250
456 $300
657 $500
657 $250

I'll need two new columns - we'll say "Acct# No Dups" and "MAX Dollar Amt" that will display:
Acct# No Dups MAX Dollar Amt
123 $200
456 $300
657 $500

Hopefully, I understood your response and this all makes sense Again, I really appreciate your help!

 

[Aladin Akyurek]

Thanks so much for your help but I think I should have been more specific. Will I have to specify which account number I'm lookin for? I really need to display one instance of each duplicated account number as well as the maximum dollar amount in any row in column O that contains that account number. For example:

Acct# Dollar Amt
123 $100
123 $200
456 $250
456 $300
657 $500
657 $250

I'll need two new columns - we'll say "Acct# No Dups" and "MAX Dollar Amt" that will display:
Acct# No Dups MAX Dollar Amt
123 $200
456 $300
657 $500

Hopefully, I understood your response and this all makes sense Again, I really appreciate your help!

Book5
	A	B	C	D	E	F
1					3
2	Acct#	Dollar Amt	0		Acct#	Dollar Amt
3	123	$100	1		123	200
4	123	$200			456	300
5	456	$250	2		657	500
6	456	$300
7	657	$500	3
8	657	$250
Sheet1

C2 must house a 0.

C3, copied down:

=IF(A3<>"",IF(ISNUMBER(MATCH(A3,$A$2:A2,0)),"",LOOKUP(9.99999999999999E+307,$C$2:C2)+1),"")

E1:

=LOOKUP(9.99999999999999E+307,C2:C8)

E3, copied down:

=IF(ROWS($F$3:F3)<=$E$1,LOOKUP(ROWS($F$3:F3),$C$3:$C$8,$A$3:$A$8),"")

F3:

=IF(N(E3),MAX(IF($A$3:$A$8=E3,$B$3:$B$8)),"")

which is confirmed with control+shift+enter (not with enter) and copied down.

OR: Build a pivot table...

Book5
	A	B	C	D
1
2	Acct#	Dollar Amt
3	123	$100
4	123	$200
5	456	$250
6	456	$300
7	657	$500
8	657	$250
9
10
11
12
13
14	Max of Dollar Amt
15	Acct#	Total
16	123	200
17	456	300
18	657	500
19	Grand Total	500
20
Sheet1