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

Some videos you may like

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)

Aladin Akyurek

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

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top