Announcement

Collapse
No announcement yet.

search for maximum value in a column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        Re: excel help search

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

        thanks again

        Comment


        • #5
          Re: excel help search

          if possible i would prefer an excel solution.
          thanks

          Comment


          • #6
            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

            LinkedIn: Will Riley

            Comment


            • #7
              Re: search for maximum value in a column

              If you were just searching for the maximum in column B the formula would be:
              =MAX(B2:B7)
              and if you wanted to know how many persons in column A had the maximum in column B:
              =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
              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.

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  LinkedIn: Will Riley

                  Comment


                  • #10
                    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

                    New users should read the Forum Rules before posting

                    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.

                    Comment


                    • #11
                      Re: search for maximum value in a column

                      Originally posted by royUK
                      If the dollar amounts are all different doesn't Barbara's Pivottable do that?
                      Yes, if 1 or more persons have the maximum, the Pivot Table works.
                      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.

                      Comment


                      • #12
                        Re: search for maximum value in a column

                        Originally posted by royUK
                        If the dollar amounts are all different doesn't Barbara's Pivottable do that?

                        I'm sure it does, but surely there is always room for an alternative solution
                        Kind Regards, Will Riley

                        LinkedIn: Will Riley

                        Comment


                        • #13
                          Re: search for maximum value in a column

                          Originally posted by WillR
                          I'm sure it does, but surely there is always room for an alternative solution
                          So very true. Depends on what the required results are. If getting only one, and probably the first occurence, is all that matters then the formula works provided as pointed out that using some formulas require that the referenced workbook be open. Pivot Tables takes the worry out of more then one occurence.

                          Different strokes for different folks, right!
                          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.

                          Comment


                          • #14
                            Re: search for maximum value in a column

                            Originally posted by Barbarr
                            Different strokes for different folks, right!
                            My point exactly... it's always cool to offer an alternative when time allows...
                            Kind Regards, Will Riley

                            LinkedIn: Will Riley

                            Comment


                            • #15
                              Re: search for maximum value in a column

                              Originally posted by Barbarr
                              So very true. Depends on what the required results are. If getting only one, and probably the first occurence, is all that matters then the formula works provided as pointed out that using some formulas require that the referenced workbook be open. Pivot Tables takes the worry out of more then one occurence.

                              Different strokes for different folks, right!
                              thanks to all. i was able to do what i needed with the formulas given.
                              thanks to Barbara for the Pivot Table. That would be perfect however i never worked with a Pivot Table so before i can use it i need to read up on it.

                              thanks again

                              Comment

                              Working...
                              X