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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=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,209
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,141,134
Messages
5,704,480
Members
421,353
Latest member
jekoxien15

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