Formula Help!?!?

TaMiller

New Member
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!!

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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

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

TaMiller

New Member
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!

MrExcel MVP
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
ABCDEF
13
2Acct#Dollar Amt0Acct#Dollar Amt
3123\$1001123200
4123\$200 456300
5456\$2502657500
6456\$300
7657\$5003
8657\$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
ABCD
1
2Acct#Dollar Amt
3123\$100
4123\$200
5456\$250
6456\$300
7657\$500
8657\$250
9
10
11
12
13
14Max of Dollar Amt
15Acct#Total
16123200
17456300
18657500
19Grand Total500
20
Sheet1

Replies
7
Views
88
Replies
3
Views
58
Replies
5
Views
75
Replies
0
Views
33
Replies
13
Views
100

1,109,444
Messages
5,528,795
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...