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



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 (NEW 2017-02-03 - Version 1.4.7.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.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

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I would like to know if I can use the Excel UDF to manipulating a .csv file without having Office installed on the PC I'm going to work...
      I read somewhere that it could be done, but I'm here to ask and be sure of what I remember... 
      I'd like to post another question...
      How can I retrieve the handle of a windows from a PID of an .exe?
      I have my script that does a ShellExecute ( which returns the PID of the .exe ), and then, switching a parameter read from a .ini file, adapt the Window on the screen ( Maximize, Minimize, On Top )...
      I tried, but without having success with this:
       
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile_x64=prova.exe #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> #include <WinAPIEx.au3> #include <Array.au3> Local $sFileConfigurazione = @ScriptDir & "\configurazione_exe.ini" If(FileExists($sFileConfigurazione)) Then Local $aSezioniIni = IniReadSection($sFileConfigurazione, "CONFIGURAZIONE_EXE") If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file: " & @CRLF & $sFileConfigurazione & @CRLF & "Errore: " & @error) Else ; Lancio dell'applicativo indicato nel file di configurazione Local $iPID = ShellExecute($aSezioniIni[1][1]) Local $hWnd If($iPID <> 0) Then Local $aWinList = WinList() For $i = 1 To $aWinList[0][0] If(WinGetProcess($aWinList[$i][1] = $iPID)) Then $hWnd = $aWinList[$i][1] EndIf Next Switch($aSezioniIni[2][1]) Case $aSezioniIni[2][1] = "MIN" WinSetState($hWnd, "", @SW_MINIMIZE) Case $aSezioniIni[2][1] = "MAX" WinSetState($hWnd, "", @SW_MAXIMIZE) Case $aSezioniIni[2][1] = "TOP" WinSetOnTop($hWnd, "", $WINDOWS_ONTOP) EndSwitch EndIf EndIf EndIf It just set on top the .exe I'm launching...
      Thanks
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I was looking for a method with rename an Excel Sheet, but I didn't find a lot...
      So, I decided to make it in another way...
      Since I have to create a new Workbook, I thought that, creating a new Workbook, deleting the existing sheet, and adding a new one, would be almost the same...
      But I'm encountering a lot of issues, both when I delete the sheet and when I add the new sheet...
      This is what I do:
       
      ; Create the Excel Object... Local $oExcel_PRV_HW = _Excel_Open(False) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante la creazione dell'oggetto Excel." & @CRLF & "Errore: " & @error & @CRLF & "Esteso: " & @extended) Else ; Create the Workbook with 1 worksheet... Local $oWorkbook_New = _Excel_BookNew($oExcel_PRV_HW, 1) ; Save the Workbook in order to open it and work with it... _Excel_BookSaveAs($oWorkbook_New, $sFilePreventivo, $xlOpenXMLWorkbook, True) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante il salvataggio della cartella di lavoro." & @CRLF & "Errore: " & @error) EndIf ; Open the Workbook to work with... Local $oWorkbook_PRV_HW = _Excel_BookOpen($oExcel_PRV_HW, $sFilePreventivo) If @error Then MsgBox($MB_ICONERROR, "Errore!", "Errore durante l'apertura del file '" & $sFilePreventivo & "'." & @CRLF & "Errore: " & @error) Else ; Here I would add the _Excel_SheetDelete() and _Excel_SheetAdd() as I did, but they return errors... EndIf EndIf Can someone help me out, please? Thanks
    • Vencejo
      By Vencejo
      Hi all,
      I think i´m doing something wrong. In the following code, _excel_RangeFind() does not find all occurrences. From sheet1 to sheet9 it does not find the occurrence of row 1, and on sheet10 it find it, but puts it last. Where am I wrong? Thank you very much and sorry for my inglish. The code:   #include <Excel.au3> Local $oAppl = _Excel_Open(True) Local $oWorkbook= _Excel_BookNew($oAppl, 10) Local $namesheet= "hoja";<-- Default name for sheet in spanish language: hoja1, hoja2, hoja3 etc. For $x= 1 to 10;<-- $x completes the name of the excel sheet: $namesheet & $x for $y= 1 to 5 _Excel_RangeWrite($oWorkbook, $namesheet & $x , "sofia" & " " & $namesheet & $x & " " & $y,"A" & $y) Next Next Local $search= _Excel_RangeFind($oWorkbook, "sof") _ArrayDisplay($search) This is using 3.3.12 version and office 2007.
    • Paranthaman
      By Paranthaman
      Hi Everyone,
      I am a beginner and I am currently learning and practicing what Autoit can do, so kindly pardon if it sound's silly.
      What my program does ----> I had written a program where I have a FOR (i=0 to n) loop which is running for n times. Inside the FOR loop, contents of array is written into excel using _Excel_RangeWrite .
      _Excel_RangeWrite($oExcelDoc, $oExcelDoc.Activesheet, $arrayname, "A1") Problem ------> During every loop run the contents of column A is only altered
      What i intend to do ------> For every loop run (i=0,1,2,3...) I want to write the array contents into respective next adjacent excel columns
      i.e) For i=0 loop, every array content should be written in A Column of excel
      For i = 1 loop, every array content should be written in B Column of excel.
       
      Can anyone give me an idea of  how can i do this? Thanks 
    • SkysLastChance
      By SkysLastChance
      Why is my code not writing X in cell D1? 
      #include <Excel.au3> Global $r = 1,$oExcel Excel () Func Excel() While ProcessExists("EXCEL.EXE") $ms = MsgBox(5,"","Process error. You have an Excel sheet open. You must close it in order to let this program work. Please close it now.") If $ms=2 Then Exit WEnd Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)") If FileExists($sExcelFile) Then $oExcel = _Excel_Open () $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file. Else $oExcel = _Excel_Open() $oWorkbook = _Excel_BookNew($oExcel, 2);this is here to create the xls file if it does not exist. EndIf EndFunc Sleep (2000) _Excel_RangeWrite($oExcel,Default, "X", "D" & $r) Exit