Formula Help!?!?

TaMiller

New Member
Joined
Oct 5, 2006
Messages
2
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
=MAX(IF(AccountsRange=Account,DollarRange))

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

TaMiller

New Member
Joined
Oct 5, 2006
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
 

Forum statistics

Threads
1,175,843
Messages
5,899,777
Members
434,798
Latest member
dragontbone

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top