kctvt

How to know max number in Excel

7 posts in this topic

Hi there, i'm looking for a script to take max number of a column in Excel.

 

Ex :  Column C , i have : 

12

13

22

123

154

....

.....

.....

134534

(About 134600 rows)


So, How to know which is the max number in Column C.

I have this code, but it take me a lot of time >"< 

So... please help me a faster code.

 

$x = 3

$CloseCheck1 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+1)
$CloseCheck2 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+2)
$CloseCheck3 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+3)
$CloseCheck4 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+4)
$CloseCheck5 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+5)
$CloseCheck6 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+6)
$CloseCheck7 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+7)
$CloseCheck8 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+8)
$CloseCheck9 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+9)
$CloseCheck10 = _Excel_RangeRead($oWorkbook, Default, "C"&$x+10)

Local $aArray = StringSplit($CloseCheck1&","&$CloseCheck2&","&$CloseCheck3&","&$CloseCheck4&","&$CloseCheck5&","&$CloseCheck6&","&$CloseCheck7&","&$CloseCheck8&","&$CloseCheck9&","&$CloseCheck10,",")
$DMAX = _ArrayMax($aArray, 1, 1)
$DMIN = _ArrayMin($aArray, 1, 1)

$n = 11
While 1
$CloseCheckn = _Excel_RangeRead($oWorkbook, Default, "C"&$x+n)

If $CloseCheckn > $DMAX Then
Global $DMAX = $CloseCheckn
EndIf

If $CloseCheckn < $DMIN Then
Global $MIN = $CloseCheckn
EndIf

If $CloseCheckn = "" Then
   ExitLoop
EndIf

$n = $n + 1
WEnd

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMAX, "P1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $DMIN, "P2")

 

 

Thanks :) 

 

 

 

 

Share this post


Link to post
Share on other sites



Take the data in an array using 

_Excel_RangeRead

and use 

_ArrayMax

to find the highest value.

1 person likes this

Share this post


Link to post
Share on other sites
7 minutes ago, Anoop said:

Take the data in an array using 

_Excel_RangeRead

and use 

_ArrayMax

to find the highest value.

 

 

Thank Anoop, I try, but it dont work. This is my code : 

Global $oAppl = _Excel_Open()
Global $sWorkbook1 = @ScriptDir & "\Test.xls"
Global $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1, Default, Default, True)

$aResult = _Excel_RangeRead($oWorkbook1, 2, "F1:F39", 1)
$max = _ArrayMax($aResult, 0, 1)
MsgBox($MB_SYSTEMMODAL, "","$max  is : " & $max)

 

 

 

 

Share this post


Link to post
Share on other sites
#include <Excel.au3>
#include <Array.au3>

Global $oAppl = _Excel_Open()
Global $sWorkbook1 = @ScriptDir & "\Test.xls"
Global $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1, Default, Default, True)

$aResult = _Excel_RangeRead($oWorkbook1)
_ArrayDisplay ($aResult)
$max = _ArrayMax($aResult, 1)
MsgBox($MB_SYSTEMMODAL, "","$max  is : " & $max)

Please try with this. Check if array displayed correctly.

1 person likes this

Share this post


Link to post
Share on other sites

Sorry,... my mistake .   

The code is worked. 

This is my errors :3 

"$aResult = _Excel_RangeRead($oWorkbook1, $oWorkbook1.Activesheet, "F1:F39", 1)|"
 

 

 

Global $oAppl = _Excel_Open()
Global $sWorkbook1 = @ScriptDir & "\Test.xls"
Global $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1, Default, Default, True)

$aResult = _Excel_RangeRead($oWorkbook1, $oWorkbook1.Activesheet, "F1:F39", 1)
$max = _ArrayMax($aResult, 0, 1)
MsgBox($MB_SYSTEMMODAL, "","$max  is : " & $max)

 

So... Thank so much Anoop

 

 

Share this post


Link to post
Share on other sites

Or you could let Excel calculate the max value:

Global $oAppl = _Excel_Open()
Global $sWorkbook1 = @ScriptDir & "\Test.xls"
Global $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1, Default, Default, True)
_Excel_RangeWrite($oWorkbook1, $oWorkbook1.Activesheet, "=MAX(F:F)", "A1", False) ; Modify the target "A1" range if needed
$iMax = _Excel_RangeRead($oWorkbook1, $oWorkbook1.Activesheet, "A1", 1) ; Modify the source "A1" range if needed
MsgBox($MB_SYSTEMMODAL, "","$iMax  is : " & $iMax)
_Excel_BookClose($oWorkbook1, False) ; Close book without saving

 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
30 minutes ago, water said:

Or you could let Excel calculate the max value:

Global $oAppl = _Excel_Open()
Global $sWorkbook1 = @ScriptDir & "\Test.xls"
Global $oWorkbook1 = _Excel_BookOpen($oAppl, $sWorkbook1, Default, Default, True)
_Excel_RangeWrite($oWorkbook1, $oWorkbook1.Activesheet, "=MAX(F:F)", "A1", False) ; Modify the target "A1" range if needed
$iMax = _Excel_RangeRead($oWorkbook1, $oWorkbook1.Activesheet, "A1", 1) ; Modify the source "A1" range if needed
MsgBox($MB_SYSTEMMODAL, "","$iMax  is : " & $iMax)
_Excel_BookClose($oWorkbook1, False) ; Close book without saving

 

Thank Water :) 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • snaileater
      _Excel_RangeInsert error ... ?
      By snaileater
      I'm trying the Excel.udf, my starting point are the examples found in the help.
      My problem is that i can't find any working example of _Excel_RangeInsert ... made many tries but i can't find the reason why ...
      New/existing Worbook nothing changes ... i tried _Excel_RangeWrite without any problem, but with _Excel_RangeInsert i always get an @error=3 and @extended=-2147352562 ...
      What could i be missing ?
      Here's the minimalistic snippet i'm playing with :
      Local $sWorkbook = @ScriptDir & "\pixel.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $dummy=_Excel_RangeInsert($oWorkbook.Activesheet, "1:3") If @error Then    $a=@extended    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Error inserting" & @CRLF & "@error = " & @error & ", @extended = " & @extended) Else    MsgBox($MB_SYSTEMMODAL, "Excel ...", "Rows successfully inserted") EndIf ConsoleWrite ($dummy & chr(13) & $a & Chr(13)) I tried every possible (...) syntax for the range object, without success ... 
      I use the latest releases of AutoIt and Office 97 ...
      Thanks for your help ...
    • PINTO1927
      Excel write background
      By PINTO1927
      Hello guys,
      through this example that you find at the end I can not get to write a text in cell A1 example, without being open excel.
      #include <Excel.au3> $oExcel = _Excel_Open(False) $sWorkbook = @DesktopDir & "\test\test.xlsx" $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")  
      while if I make "_Excel_Open(True)" visible, the text is written.
    • SorryButImaNewbie
      AutoIt script turns off, because of array is out of bound
      By SorryButImaNewbie
      Hello all!
      I wrote a little script to automate some of my work (for which I'm greatfull).
      I have little problem now. My script get data of currency values from specified time period. The problem, if XY day is a holiday or something, then the bank doesn't have new values for that day, thus my script can ask for "till that date" if there is only data for "till that date-1".
      I should be done with this for tomorrow and I should learn for my test paper tomorrow, make a genetic algorhytm homework, and at least look at a ppt...
      So here is my code, pleas ask if you need more information!
      InternetRead2() ;Creating Date and Arfolyam arrays, using DateInterval read from above (_ArraysDisplayed and function is "tested") ;Calculate and write HUF*OSSZEG Do Local $SZAMLATELJOlvaso = _Excel_RangeRead($ExcelObject, Default, $SZAMLATELJoszlop & $CellaOlvasoSzamlalo) Local $Time = StringTrimRight($SZAMLATELJOlvaso, 6) $DateArrayTimeIndex = _ArraySearch($DateArray, $Time, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-1, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-2, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-3, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf If @error = 6 Then $DateArrayTimeIndex = _ArraySearch($DateArray, $Time-4, 0, 0, 0, 0, 1) ;MsgBox(64, "Értesítés", $DateArrayTimeIndex) EndIf $DateArrayTimeIndex = $DateArrayTimeIndex+1 ;Egyébként arraysearch -1et ad vissza, 0nál. Passz Sleep(200) If Not $SZAMLATELJOlvaso = "" Then _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$DateArrayTimeIndex]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo) If @error Then _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$oXML_Node.Length]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo) MsgBox(64, "Értesítés", "Valamilyen okból, nem találok az adott dátumhoz árfolyamot, a keresési idők közül a legutolsó árfolyammal töltöttem ki!" & @CRLF & Number($oXML_Node_Datum)) EndIf EndIf $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1 Until $SZAMLATELJOlvaso = "" also my internetread2 function :
      Func InternetRead2() ;ezt használja az excelmove ;----------------------------------- ;Original Source: https://www.autoitscript.com/forum/topic/184884-solved-basic-com-help-working-on-string-returned-from-api-mi-doing-this-okey/ ;by Genius257 ;TO DO: Some error handling maybe: what if there is no $MinTime/$MaxTime? ;Read the returns in to array for future use --> Right now, 2 arrays one with the dates and one with the exchangerates ;----------------------------------- ;Globals decleared to avoid warnings Global $MinTime ;20160601000000 Global $MaxTime ;20160610000000 Global $MinTimeFormated = StringTrimRight($MinTime, 6) Global $MaxTimeFormated = StringTrimRight($MaxTime, 6) $oHTTP = ObjCreate("WinHttp.WinHttpRequest.5.1") If $MaxTimeFormated = "" and $MinTimeFormated = "" Then MsgBox(64, "Értesítés", "Nincsenek beolvasott dátumok a memóriában, dummy dátumokat adok meg!" & @CRLF & "20160901, 20160910") $MinTimeFormated = "20160901" $MaxTimeFormated = "20160905" EndIf $oHTTP.Open("GET", "http://api.napiarfolyam.hu/?bank=mnb&valuta=eur&datum="&$MinTimeFormated&"&datumend="&$MaxTimeFormated&"", False) $oHTTP.Send() $sXML = $oHTTP.responseText Global $oXML = ObjCreate("Microsoft.XMLDOM") $oXML.loadXML( $sXML ) $oXML_Nodes = $oXML.SelectNodes("./arfolyamok/deviza/item") Global $ArfolyamArray[$oXML_Nodes.Length] ;MsgBox(64, "Title", "" & $oXML_Nodes.Length & "") ;_ArrayDisplay($ArfolyamArray) Global $DateArray[$oXML_Nodes.Length] For $i=0 To $oXML_Nodes.Length-1 Global $oXML_Node = $oXML_Nodes.Item($i) $oXML_Node_Bank = $oXML_Node.SelectNodes("./bank") $oXML_Node_Bank = $oXML_Node_Bank.Length>0?$oXML_Node_Bank.Item(0).text:"" Global $oXML_Node_Datum = $oXML_Node.SelectNodes("./datum") $oXML_Node_Datum = $oXML_Node_Datum.Length>0?$oXML_Node_Datum.Item(0).text:"" $oXML_Node_Penznem = $oXML_Node.SelectNodes("./penznem") $oXML_Node_Penznem = $oXML_Node_Penznem.Length>0?$oXML_Node_Penznem.Item(0).text:"" $oXML_Node_Kozeps = $oXML_Node.SelectNodes("./kozep") $oXML_Node_Kozep01 = $oXML_Node_Kozeps.Length>0?$oXML_Node_Kozeps.Item(0).text:"" $oXML_Node_Kozep02 = $oXML_Node_Kozeps.Length>1?$oXML_Node_Kozeps.Item(1).text:"" $ArfolyamArray[$i] = $oXML_Node_Kozep01 $DateArray[$i] = StringTrimRight(StringReplace($oXML_Node_Datum, "-", ""), 8) ConsoleWrite( "Match [" & StringFormat("%02i", $i+1) & "]:"&@CRLF& _ @TAB&"Bank: "&@TAB&$oXML_Node_Bank&@CRLF& _ @TAB&"Datum: "&@TAB&$oXML_Node_Datum&@CRLF& _ @TAB&"Penznem: "&@TAB&$oXML_Node_Penznem&@CRLF& _ @TAB&"Kozep01: "&@TAB&$oXML_Node_Kozep01&@CRLF& _ @TAB&"Kozep02: "&@TAB&$oXML_Node_Kozep02&@CRLF _ ) Next ;_ArrayDisplay($ArfolyamArray) ;_ArrayDisplay($DateArray) EndFunc My problem is that I have dates in my excel till 2016.10.30, but I only have dates till 28.
      (see the api, according to aoutit console, I have the same data: http://api.napiarfolyam.hu/?bank=mnb&valuta=eur&datum=20161001&datumend=20161030 )
      My idea/goal (with the If @error then... after @SZAMLATELJolvaso part) is that if this happens, I want it to automaticly jump to the last date of the array (I will be able to write something more elaborate, but for now thats my goal, to save the sinking ship )
      Thank yu for your help and insight!
    • Masum
      Excel write in cell not replace
      By Masum
      Hi all,
      Is there a way to write a string to a cell in Excel without replacing what may be already in that cell? Can this be achieved with a single function? Or will I need to read any potential data first, then join it, and then write to that cell? As the write function replaces what is already in the cell.
      Thanks
    • l3ill
      _Excel_RangeFind - requested action with this object has failed
      By l3ill
      Hi,
      anybody know what this is about?
      I have tried on three different PC's all give me the same error whether my own code or the Help File Examples...
      All other excel func's work fine. ( haven't tested them all but most)
      >Running:(3.3.14.2):C:\Users\XXXXX\Desktop\autoit-v3\install\autoit3.exe "C:\Users\XXXXX\Desktop\autoit-v3\install\Examples\Helpfile\_Excel_RangeFind[2].au3" --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop "C:\Users\XXXXX\Desktop\autoit-v3\install\Include\Excel.au3" (656) : ==> The requested action with this object has failed.: $aResult[$iIndex][1] = $oMatch.Name.Name $aResult[$iIndex][1] = $oMatch^ ERROR No script error to speak of just this Include error.
      Any help is greatly appreciated.
      Bill