Previous Topic Next Topic

EXCEL 2007 FIND MAX VALUE

Solved/Closed
bigband Posts 11 Registration date Friday June 11, 2010 Status Member Last seen October 25, 2012 - Jun 13, 2010 at 11:59 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 17, 2010 at 09:26 AM
Hello,
date time value
05/11/2008 205 102 FALSE
05/11/2008 205 110 TRUE
05/11/2008 205 108 FALSE
05/11/2008 240 123 TRUE
05/11/2008 240 104 FALSE
05/11/2008 240 45 FALSE
06/11/2008 110 108 TRUE
06/11/2008 110 105 FALSE
06/11/2008 140 110 FALSE
06/11/2008 140 114 FALSE
06/11/2008 140 122 TRUE
07/11/2008 140 69 FALSE
07/11/2008 140 98 TRUE
07/11/2008 210 120 TRUE
07/11/2008 210 92 FALSE
I have 3 lists in cols a,b,c,I wish to put a formula in col d which will give the maximum value in col c against each corresponding date&time showing true or false as shown,to enable me to sort by max values.
Many Thanks



Related:

6 responses

Answer 1 / 6
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 13, 2010 at 02:05 PM
When you say sort by MAX value. Do you mean to do this sort as


column A ascending
Column B Ascending
Column C descending


or you mean to sort as

Column C
Coumn A
COlumn B
Answer 2 / 6
bigband Posts 11 Registration date Friday June 11, 2010 Status Member Last seen October 25, 2012
Jun 14, 2010 at 03:26 AM
Hi
After using a formula all my maximum values in col c will be indicated by a true cell in col d,I then wish to select all and sort by col d to give me all my true cells(my max values) together.
05/11/2008 205 102 FALSE
05/11/2008 205 108 FALSE
05/11/2008 240 104 FALSE
05/11/2008 240 45 FALSE
06/11/2008 110 105 FALSE
06/11/2008 140 110 FALSE
06/11/2008 140 114 FALSE
07/11/2008 140 69 FALSE
07/11/2008 210 92 FALSE
05/11/2008 205 110 TRUE
05/11/2008 240 123 TRUE
06/11/2008 110 108 TRUE
06/11/2008 140 122 TRUE
07/11/2008 140 98 TRUE
07/11/2008 210 120 TRUE
date time value
Many Thanks
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 14, 2010 at 05:21 AM
How many rows are we talking about here ?

You can use array formula for reasonable number of rows. Reasonable depends on how slow and fast you can live with

To enter array formula, type in the formula AND PRESS CTRL + SHIFT + ENTER at same time
=IF(MAX(IF(B:B=B2,C:C))=C2, TRUE, FALSE)

If all is ok on the formula bar, you should see that now your formula is enclosed with in {}

Drag the formula down
Then you can use copy - pastespecial as values to eliminate the formula
Answer 3 / 6
bigband Posts 11 Registration date Friday June 11, 2010 Status Member Last seen October 25, 2012
Jun 14, 2010 at 11:02 AM
Excellent,the formula works great,but i can only work a small number of rows at a time,otherwise it locks.I have 70000 rows in 20 separate worksheets.
Many Thanks
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 14, 2010 at 11:18 AM
Yeah as I warned that number of rows is important. It seems that you would need to use user defined function

Are you looking for on fly or are you looking for a at demand sort of results.
Answer 4 / 6
bigband Posts 11 Registration date Friday June 11, 2010 Status Member Last seen October 25, 2012
Jun 16, 2010 at 09:46 AM
Sorry,not computer literate enough to know these definitions,I guess i will persevere with the formula you kindly provided.The database is for a hobby not a
business application.
Many Thanks
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 16, 2010 at 09:49 AM
By that I was asking. Are you looking for a solution where, you click a button or run a code when ever you feel like and get the results. Or you are looking for a solution where you would enter a value/ or change a value and the results are instanteously updated.
Answer 5 / 6
bigband Posts 11 Registration date Friday June 11, 2010 Status Member Last seen October 25, 2012
Jun 17, 2010 at 06:34 AM
click a button or run a code when ever you feel like and get the results.would seem to be the easiest solution.It does not need to be a true or false statment
in col d ,anything that identifies the maximum value in col c against each corresponding date&time.
Many Thanks
Answer 6 / 6
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 17, 2010 at 09:26 AM
Try this one

This is the macro that will do what I think you wanted

Open your excel workbook
press ALT + F11 to launch VBE
Click on Insert and add a module
Copy the code between lines (both lines including)

Sub SortSpecial()

..

End Sub

Paste the code in the added module

To run the code, click on RUN or PRESS F5
Just make sure that the active sheet when you run the macro, is the one that you want to address. 

Sub SortSpecial()
Dim Cell As Range


    Set Cell = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Cell Is Nothing Then Exit Sub
    
    If Cell.Row < 2 Then Exit Sub
    
    Cells.Sort _
            Key1:=Range("A2"), Order1:=xlAscending, _
            Key2:=Range("B2"), Order2:=xlAscending, _
            Key3:=Range("C2"), Order3:=xlDescending, _
            Header:=xlYes, MatchCase:=False
            
    Set Cell = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
         
    With Range(Cells(2, "D"), Cells(Cell.Row, "D"))
        .FormulaR1C1 = "=IF(OR(RC1<> R[-1]C1, RC2<> R[-1]C2), TRUE,FALSE)"
        .Copy
        .PasteSpecial xlPasteValues
    End With
       
    Cells.Select
    Cells.Sort _
            Key1:=Range("D2"), Order1:=xlDescending, _
            Key2:=Range("A2"), Order2:=xlAscending, _
            Key3:=Range("B2"), Order3:=xlAscending, _
            Header:=xlYes, MatchCase:=False
   
   Set Cell = Nothing
   Range("A2").Select
   
End Sub

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses