Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: search for maximum value in a column

  1. #1
    Join Date
    17th October 2005
    Posts
    29

    search for maximum value in a column

    hi, i'm new to this and need your help.

    i have a simple excel sheet where col A values are names,
    and col B values are the amount in dollars.
    how can i search for the max value in column B and show who that is from col A.

    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: excel help search

    Hi and welcome to OzGrid.

    It's possible that more than 1 person in Column A has the maximum amount in Column B. Do you want all persons or any person?
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  3. #3
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: excel help search

    I've moved your thread to the proper forum. Please ensure that you post to the correct or most appropriate forum.

    Do you want an Excel or VBA solution?
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  4. #4
    Join Date
    17th October 2005
    Posts
    29

    Re: excel help search

    thanks,
    it may be possible that more than 1 person has the same max value,

    thanks again

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th October 2005
    Posts
    29

    Re: excel help search

    if possible i would prefer an excel solution.
    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: excel help search

    Kiz,

    Welcome to Ozgrid. Please can you make your question titles relevant to your problem

    excel help search is not very helpful, so I have edited it.

    This helps people to help you and others find solutions to their problems.

    Thanks.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  7. #7
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: search for maximum value in a column

    If you were just searching for the maximum in column B the formula would be:
    VB:
    =MAX(B2:B7) 
    
    
    and if you wanted to know how many persons in column A had the maximum in column B:
    VB:
    =COUNTIF(B2:B7,MAX(B2:B7)) 
    
    
    I've used a Pivot Table to look at columns A and B and show me the persons with the maximum. When you've added more data or edited the data in columns A and B, right-click on any cell in the Pivot Table and click Refresh Data to update the Pivot Table.

    You see as you hang out here that there are often more than 1 way to do something.

    Using VBA, you could have a worksheet change event macro that could run certain code whenever a cell in column A or B changed.

    This could be used to update the pivot table everytime a cell in either column A or B changed.

    Alternatively, the code could list the persons in column D whose amount in column B is equal to the maximum in column B.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  8. #8
    Join Date
    17th October 2005
    Posts
    29

    Re: search for maximum value in a column

    thanks it's a great help.
    what i need is to get the name of the person with max value,and
    then display the name in the worksheet, so that say in
    col C i have the name and col D the amount of the revenue.
    chances are there will be no 2 dollar amounts equal.

    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: search for maximum value in a column

    Let's assume your names are in A2:A5, with the values in B2:B5

    Use

    =INDEX($A$2:$B$5,MATCH(MAX($B$2:$B$5),$B$2:$B$5,0),1)

    To get the max name - this will work for ONE maximum value

    For multiple instances, the first name will be returned, but you could use barb's contif founction to provide a warning for when this happens.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  10. #10
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,446

    Re: search for maximum value in a column

    If the dollar amounts are all different doesn't Barbara's Pivottable do that?
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Delete Rows After Maximum Value In Column
    By leafman in forum EXCEL HELP
    Replies: 1
    Last Post: May 9th, 2008, 00:50
  2. Return Heading Of Column With Maximum Value
    By Elmura in forum EXCEL HELP
    Replies: 4
    Last Post: September 17th, 2007, 12:09
  3. Looking Up Cell Of Maximum Value In Another Column
    By SamBlacker in forum EXCEL HELP
    Replies: 3
    Last Post: April 26th, 2007, 21:10
  4. Take Value To Left Of Maximum Value In A Column
    By Mezari in forum EXCEL HELP
    Replies: 2
    Last Post: November 21st, 2006, 13:08
  5. Add to Maximum in Column
    By rexy_sti555 in forum EXCEL HELP
    Replies: 10
    Last Post: July 10th, 2006, 14:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno