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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 17, 2010 at 09:26 AM
Related:
- EXCEL 2007 FIND MAX VALUE
- Save as pdf office 2007 - Download - Other
- Hbo max download pc - Download - Movies, series and TV
- Sound max - Download - Drivers
- Free fire max download - Download - Battle royale
- Max repel cheat code - Guide
6 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 13, 2010 at 02:05 PM
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
column A ascending
Column B Ascending
Column C descending
or you mean to sort as
Column C
Coumn A
COlumn B
bigband
Posts
11
Registration date
Friday June 11, 2010
Status
Member
Last seen
October 25, 2012
Jun 14, 2010 at 03:26 AM
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
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
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
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
bigband
Posts
11
Registration date
Friday June 11, 2010
Status
Member
Last seen
October 25, 2012
Jun 14, 2010 at 11:02 AM
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
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
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.
Are you looking for on fly or are you looking for a at demand sort of results.
bigband
Posts
11
Registration date
Friday June 11, 2010
Status
Member
Last seen
October 25, 2012
Jun 16, 2010 at 09:46 AM
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
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
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.
Didn't find the answer you are looking for?
Ask a question
bigband
Posts
11
Registration date
Friday June 11, 2010
Status
Member
Last seen
October 25, 2012
Jun 17, 2010 at 06:34 AM
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
in col d ,anything that identifies the maximum value in col c against each corresponding date&time.
Many Thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 17, 2010 at 09:26 AM
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.
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
