Compare values in Excel

[Nilkimas]

Dear Hivemind, 

 

I am looking for a bit of help. 

As part of a project I need to go through an excel file and use some things from there in a different program. There are several operations that the script will need to do, so I am using a GUI to call on different functions. 

At the moment I am looking into a way to get a number for the amount of repeats in a row. 

So if there is just one and no repeats, it should return 1

If there are 4 in a row, it should return 4 etc. 

At the moment I am stuck as I keep getting an error that a variable needs to be an Object. I am still trying to wrap my head about the language. Any help that you guys can provide would be appreciated. 

 

At the moment I have the script as follows:

expandcollapse popup

#include <WindowsConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#AutoIt3Wrapper_UseX64=n ; In order for the x86 DLLs to work
#include "GUIConstantsEx.au3"
#include "OpenCV-Match_UDF.au3"

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

Local $vBuyerID  ; Formula of the read cell
Local $vTransID
Local $vOrderID
Local $vItemID
Local $vAmount
Local $vCurr
Local $vReason
Local $oWorkbook  ; Object of the Excel workbook to process
Global $iLine = 3    ; Line number to read
Local $iWidthCell = 350
Local $iAdd = 1
Local $iLine2

$vBuyerID = $oWorkbook.ActiveSheet.Cells($iLine,1).FormulaR1C1
$vTransID = $oWorkbook.ActiveSheet.Cells($iLine,2).FormulaR1C1
$vOrderID = $oWorkbook.ActiveSheet.Cells($iLine,3).FormulaR1C1
$vItemID = $oWorkbook.ActiveSheet.Cells($iLine,4).FormulaR1C1
$vAmount = $oWorkbook.ActiveSheet.Cells($iLine,9).FormulaR1C1
$vCurr = $oWorkbook.ActiveSheet.Cells($iLine,8).FormulaR1C1
$vReason = $oWorkbook.ActiveSheet.Cells($iLine,7).FormulaR1C1
$iLine2 = $iLine + $iAdd
$vTransID2 = $oWorkbook.ActiveSheet.Cells($iLine2,2).FormulaR1C1

UserInterFace()

Func UserInterFace()
Local $vBuyerID  ; Formula of the read cell
Local $vTransID
Local $vOrderID
Local $vItemID
Local $vAmount
Local $vCurr
Local $vReason
Local $oWorkbook  ; Object of the Excel workbook to process
Global $iLine = 3    ; Line number to read
Local $iWidthCell = 350
Local $iAdd = 1
Local $iLine2
    Local $hGUI = GUICreate("MulitiTool2.0", 400,380)
                                Local $idButton_Add = GUICtrlCreateButton("Process", 10, 10)
                                Local $idButton_Close = GUICtrlCreateButton("Exit", 210, 180)
                                Local $idButton_MacNote = GUICtrlCreateButton("Place MacNote", 210, 10)

        GUISetState(@SW_SHOW, $hGUI)

    Local $iPID = 0
    
            ; Loop until the user exits.
        While 1
                Switch GUIGetMsg()
                        Case $GUI_EVENT_CLOSE
                                ExitLoop
                        Case $idButton_Add
                                Looptest()
                        Case $idButton_MacNote
                                MsgBox(4096+16, "Line", "Line number  " & $Line "runs until  " & $Line2)
                        Case $idButton_Close
                                MsgBox($MB_SYSTEMMODAL, "", "the closing button has been clicked", 2)
                                Exit
                EndSwitch
        WEnd

        ; Delete the previous GUI and all controls.
        GUIDelete($hGUI)
        
        _OpenCV_Shutdown();Closes DLLs

        ; Close the Notepad process using the PID returned by Run.
        If $iPID Then ProcessClose($iPID)
EndFunc   ;==>Example


Looptest()

Func Looptest()

Local $vTransID
Local $oWorkbook  ; Object of the Excel workbook to process
Local $iAdd = 1
Local $iLine2
$vTransID = $oWorkbook.ActiveSheet.Cells($iLine,2).FormulaR1C1
$iLine2 = $iLine + 1

    If $TransID = $TransID2 then $Line2 = $Line Else
                Do
                    $iLine2 = $iLine2 + 1 ; Or $i += 1 can be used as well.
                Until $TransID <> $TransID2 

EndFunc

 

[water]

You could have a look at the CountIf function. Insert this function with your script and let do Excel the calculation, then extract the results.

[Nilkimas]

Sadly that won't work. 

What I need is to get the order ID from excel, orders can have between 1 and 30 items. 

If there is 1, I need to do thing in a program and fill in a number from that line in a specific place. 

If there are more, then it needs to do the same initial thing and then repeat entering numbers on successive lines in a specific place as above. 

For example

line 4 is order number 1234 value 5

line 5 is order number 1235 value 6

line 6 is order number 1235 value 3

line 7 is order number 1235 value 8

line 8 is order number 1237 value 10

line 9 is order number 1237 value 100

What I want to do it open the script, press a button, do mouse move and clicks (can't control directly), fill in the value in a specific field. Once done reset the program and do it again. I also do not want to fully automate it. 

If there are multiple values per order number I need to do the mouse moves and clicks first and then I can enter the value, press tab and enter the next and so on. 

I hope that makes it clearer what I am trying to achieve. 

[Nilkimas]

Does anyone have any idea? I know it is the holiday period

[Nine]

Might help if you post the excel file and have a simpler script that shows the error you are having.  Just saying...

[Nilkimas]

The code in question is this (it has been trimmed down (the trimming down helped as I am no longer getting the error):

expandcollapse popup

#include <WindowsConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#AutoIt3Wrapper_UseX64=n ; In order for the x86 DLLs to work
#include "GUIConstantsEx.au3"
#include "OpenCV-Match_UDF.au3"

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test2.xlsx")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

Local $vBuyerID
Local $vItem
Local $vOrderID
Local $vAmount
Global $iLine = 2    ; Line number to read
Local $iWidthCell = 350
Local $iLine2

$vBuyerID = $oWorkbook.ActiveSheet.Cells($iLine,1).FormulaR1C1
$vItem = $oWorkbook.ActiveSheet.Cells($iLine,2).FormulaR1C1
$vOrderID = $oWorkbook.ActiveSheet.Cells($iLine,3).FormulaR1C1
$vAmount = $oWorkbook.ActiveSheet.Cells($iLine,4).FormulaR1C1
$iLine2 = $iLine + 1
$vOrderID2 = $oWorkbook.ActiveSheet.Cells($iLine2,3).FormulaR1C1

UserInterFace()

Func UserInterFace()
Local $vBuyerID
Local $vItem
Local $vOrderID
Local $vAmount
Global $iLine = 2    ; Line number to read
Local $iWidthCell = 350
Local $iAdd = 1
Local $iLine2
    Local $hGUI = GUICreate("MulitiTool2.0", 400,380)
                                Local $idButton_Add = GUICtrlCreateButton("Process", 10, 10)
                                Local $idButton_Close = GUICtrlCreateButton("Exit", 210, 180)
                                Local $idButton_MacNote = GUICtrlCreateButton("Lines", 210, 10)

        GUISetState(@SW_SHOW, $hGUI)

    Local $iPID = 0
    
            ; Loop until the user exits.
        While 1
                Switch GUIGetMsg()
                        Case $GUI_EVENT_CLOSE
                                ExitLoop
                        Case $idButton_Add
                                Looptest()
                        Case $idButton_MacNote
                                MsgBox($MB_SYSTEMMODAL, "", $iLine & " And " & $iLine2)
                        Case $idButton_Close
                                MsgBox($MB_SYSTEMMODAL, "", "the closing button has been clicked", 2)
                                Exit
                EndSwitch
        WEnd

        ; Delete the previous GUI and all controls.
        GUIDelete($hGUI)
        
        _OpenCV_Shutdown();Closes DLLs

        If $iPID Then ProcessClose($iPID)
EndFunc   ;==>Example


Looptest()

Func Looptest()

Local $vBuyerID
Local $vItem
Local $vOrderID
Local $vAmount
Local $iLine2
$vOrderID = $oWorkbook.ActiveSheet.Cells($iLine,3).FormulaR1C1
$iLine2 = $iLine + 1
$vOrderID2 = $oWorkbook.ActiveSheet.Cells($iLine2,3).FormulaR1C1

    If $vOrderID = $vOrderID2 Then 
        $Line2 = $iLine 
            Else
                Do
                    $iLine2 += 1
                Until $vOrderID <> $vOrderID2 
    EndIf
EndFunc

I am no longer getting an error, I am not getting the result I want either sadly. 

I should be getting for the first time clicking the process button and then pressing lines "2 and 3".  It gives "2 And"

The file I am checking has been attached.  
 

 

test2.xlsx

Edited December 29, 2022 by Nilkimas
Fixed Excel file

[Nine]

Sorry to say but your code is badly written.  I hardly can understand what you are trying to achieve here.  So base on the excel file you gave us can you explain what you want to calculate ?  Is it possible that you want to sum the number of 1s in column C by pressing some buttons, then sum the number of 2s and so on ?

[Nilkimas]

I just started writing in AutoIt, so that is why it is badly written. Still in the early stages in figuring out how it works.

Also I am not a programmer, just someone who is using building blocks he understands to build something that does the job.  

What I want the script to do is enter the value of an orderid in a field in a different program. 

If the orderid is the same I need there will be multiple value fields in the other program and the script will need to write one line at the time until the orderid's are no longer matching. 

I hope that makes it more clear. 

I have a working script in AutoHotKey, but I need to recreate it in AutoIt. 

Edited December 29, 2022 by Nilkimas
Added clarification.

[Nine]

Clear as mud.  No idea what you are talking about.  What is this "different program" ?

I am really trying to understand what you want so I can help you, but at this stage I simply cannot do anything without a proper explanation.

I suspect English is not your native language, maybe using google translate could help ?

[Nilkimas]

I think my English is quite ok actually. 

What I want to do is the following: 
Go through the excel file, one OrderID at the time. The OrderID can have between 1 and 30 items. 

I need to enter the value of those items in a field in (lets call it) Application X, the number of fields for the values is dependent on the number of items in the order. 

If there is 1 item, it will be 1 field, if there are 10 there will be 10. 

Application X has been written by developers who are evil (in my humble opinion) and they make the life of users like me difficult. That is why I am making this script to help me (and my team) process orders a lot quicker than I can do by hand. There is a bit of scrolling involved and clicking of buttons, once per order, not per item. I can not bypass Application X sadly. 

In my AutoHotKey script I used the following: 

;for multiple items in the same order, we do this check and decide on which version to use, either entering a single item or a multi-item order
LineCheck := Line +1 ;this is declaring a variable
A3 := test.ActiveSheet.cells.(Line,3).FormulaR1C1
Check := test.ActiveSheet.cells.(LineCheck,3).FormulaR1C1

If (A3 != Check) ;this would be a single item per order
{
Goto Step4
}
if (A3 == Check) ;multi item order
{
Goto Step5
}
If A3 = ""
{
MsgBox All done at line %line%
}
else return

Step5: ;This would be similar to Func in AutoIt. 
FirstM := Line ;again variable
FirstM0 := Line ;this sets the base for the entries later
Loop { ;as long as it matches it goes on until it hits 1000
Line3 := Line +1
A3 := test.ActiveSheet.cells.(Line,3).FormulaR1C1 ;etc
Check := test.ActiveSheet.cells.(Line3,3).FormulaR1C1
if (A3 != Check) ;if it no longer matches, it goes on to the next step. 
Goto NoMoreMatches
Line +=1
}
until (Line2=1000)
goto Error

NoMoreMatches: 
Var3 := Line - FirstM +1 ;this is the loop count for later

It first checks if there is a repeat of the OrderID, if not the script does the clicking and enters the value into a field in Application X. 

If there are multiple items, the script checks how many there are. That is the value that is used in a loop function later. The scrolling and clicking will be the same for both single item and multi item orders. The loop function will repeat the read/write into the fields as required, luckily a simple tab (or 2) is enough to advance to teh next field and fill it in. 

I don't think I can explain it more clearly. 

[ioa747]

maybe it helps 

Local $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.open(@ScriptDir & "\test1.xls") ; open file

Local $OrderId, $index = 2

While 1
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop
    To_ApplicationX($OrderId)
    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("[" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

[Nilkimas]

It helps  little, as far as I understand this will keep writing $OrderID until the field is empty. 

That is only part of what I need sadly, I need to compare the order ID on line 2 with the one on line 3. If they are the same then I can do them one after another. 

If they are not the same I need to do the one on line 2 only. Then reset Application X before I can go onto line 3. 

[ioa747]

16 minutes ago, Nilkimas said:

I need to compare the order ID on line 2 with the one on line 3

and what with   the order ID on line 3 with the one on line 4 ?

[ioa747]

 something like this ?

Local $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.open(@ScriptDir & "\test1.xls") ; open file

Local $OrderId, $OrderNext, $Reset = False, $index = 2

While 1
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop

    $OrderNext = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index + 1, 3).Value

    To_ApplicationX($OrderId)

    If $OrderId <> $OrderNext Then
        $Reset = True
        ConsoleWrite("reset Application = " & $Reset & @CRLF)
    EndIf

    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("[" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

[ioa747]

  so is better

Local $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.open(@ScriptDir & "\test1.xls") ; open file

Local $OrderId, $OrderNext, $Reset = False, $index = 2

While 1
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop

    $OrderNext = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index + 1, 3).Value

    To_ApplicationX($OrderId)

    If $OrderId <> $OrderNext Then
        $Reset = True
        ConsoleWrite("reset Application = " & $Reset & @CRLF)
        $index += 1
    EndIf

    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("[" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

[Nilkimas]

I'll have to give it a try, but it looks like I can build on this. 

Thank you very much. 

[ioa747]

try this

Local $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.open(@ScriptDir & "\test1.xls") ; open file

Local $OrderId, $OrderNext, $Reset = False, $index = 2

While 1
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop

    $OrderNext = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index + 1, 3).Value

    To_ApplicationX($OrderId, $index)

    If $OrderId <> $OrderNext Then
        $Reset = True
        ConsoleWrite("reset Application = " & $Reset & @CRLF)
;~      $index += 1
    EndIf

    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes, $Line)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("from line " & $Line & " [" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

Edited December 29, 2022 by ioa747

[Nilkimas]

Sadly this isn't what I was looking for. 

I added to the code as I want to use a GUI in order to run the script. 

expandcollapse popup

#include <WindowsConstants.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#AutoIt3Wrapper_UseX64=n ; In order for the x86 DLLs to work
#include "GUIConstantsEx.au3"
#include "OpenCV-Match_UDF.au3"

Opt("TrayAutoPause", 0)

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test2.xlsx")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

Opt("GUIOnEventMode", 1) ; Change to OnEvent mode

Local $hGUI = GUICreate("MulitiTool2.0", 400,380)
Local $vOrderID 
Local $vAmount 
Local $Index = 1
$vOrderID = $oWorkbook.ActiveSheet.Cells($Index,3).value
$vAmount = $oWorkbook.ActiveSheet.Cells($Index,4).value

Local $iOKButton = GUICtrlCreateButton("OK", 70, 50, 60)
GUICtrlSetOnEvent($iOKButton, "OKButton")

GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEButton")


GUICtrlCreateLabel("Order ID "&$vOrderID, 10, 100)
GUICtrlCreateLabel("Line1 "&$Index, 10, 130)

GUISetState(@SW_SHOW, $hGUI)

While 1
        Sleep(10) ; Sleep to reduce CPU usage
WEnd

Func OKButton()
    While 1
        $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
        If $OrderId = "" Then ExitLoop

        $OrderNext = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index + 1, 3).Value

        To_ApplicationX($OrderId, $index)

        If $OrderId <> $OrderNext Then
            $Reset = True
            WinActivate ("Document1")
            Send("reset Application = " & $Reset & @CRLF)
;~      $index += 1
    EndIf

        $index += 1
    WEnd
EndFunc

Func CLOSEButton()
        ; Note: at this point @GUI_CtrlId would equal $GUI_EVENT_CLOSE,
        ; @GUI_WinHandle will be either $hMainGUI or $hDummyGUI
        If @GUI_WinHandle = $hGUI Then
                MsgBox($MB_OK, "GUI Event", "You selected CLOSE in the main window! Exiting...")
                Exit
        EndIf
EndFunc   ;==>CLOSEButton 

Func To_ApplicationX($RepeatTimes, $Line)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    WinActivate ("Document1")
    Send("from line " & $Line & " [" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

It is giving me the following output: 

From line 1 [orderId]

Reset Application = True

From line 2 [10] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,

From line 3 [10] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,

Reset Application = True

From line 4 [20] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,

From line 5 [20] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,

Reset Application = True

From line 6 [30] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30,

Reset Application = True

From line 7 [40] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40,

Reset Application = True

From line 8 [50] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,

From line 9 [50] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,

From line 10 [50] 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50,

 

Instead, based on the same initial file I would like to get: 

On first press of Ok: 

10 (as the orderID)

Then the script will do other things, followed by writing

1496

10200

Second press of OK:

20 

script does things

5

100020

Third press: 

30

Does things 

5

Fourth press: 

40

Does things

134.3

Fifth press:

50

Does things

123.45

98.21

23.79

 

I hope that this makes it more clear. 

The 'does things' part is mainly to press buttons in Application X and wait for things to load. 

[ioa747]

expandcollapse popup

Local $oExcel = ObjCreate("Excel.Application") ; Create an Excel Object
$oExcel.Visible = 1 ; Let Excel show itself
$oExcel.WorkBooks.open(@ScriptDir & "\test1.xlsx") ; open file

Local $OrderId, $OrderNext, $Reset = False, $index = 2
Local $iFrom = $index
While 1
    $OrderId = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index, 3).Value
    If $OrderId = "" Then ExitLoop

    $OrderNext = $oExcel.ActiveWorkBook.ActiveSheet.Cells($index + 1, 3).Value

    To_ApplicationX($OrderId, $index)

    If $OrderId <> $OrderNext Then
        $Reset = True
        ConsoleWrite("reset Application = " & $Reset & " --> ")
            For $i = $iFrom to $index
                ConsoleWrite($oExcel.ActiveWorkBook.ActiveSheet.Cells($i, 4).Value & "; ")
            Next
            ConsoleWrite( "" & @CRLF)
            $iFrom = $i

;~      $index += 1
    EndIf

    $index += 1
WEnd

Exit

Func To_ApplicationX($RepeatTimes, $Line)
    Local $Msg
    For $i = 1 To Int($RepeatTimes)
        $Msg &= $i & ", "
    Next
    ConsoleWrite("from line " & $Line & " [" & $RepeatTimes & "] " & $Msg & @CRLF)
EndFunc   ;==>To_ApplicationX

 

Edited January 16, 2023 by ioa747

[GMK]

Would a pivot table not work in this situation?