Delete multidimensional array row based on criteria

[Althalus]

I am trying to delete rows from my multidimensional array.  With my code I am not getting any errors but it is obvious that it is not finishing.  Would anyone be able to give me some pointers on what I am doing wrong between lines 21 to 28?

#include <File.au3>
#include <Excel.au3>
#include <Array.au3>


;-----------------------------------------------------------------------------------------------------------------------------
; Open/Read/Manipulate the Excel File
;-----------------------------------------------------------------------------------------------------------------------------
Global $excelFile = FileOpenDialog("Open File That Is To The Clearinghouse", @ScriptDir & "\", "Excel files (*.xlsx)", 1)

; Open User Specified Excel file
Global $openExcelFile = _ExcelBookOpen($excelFile,1,True)

; Message to inform user something is happening...
MsgBox(0, "Reading File", "Reading File. Please wait.", 2)

; Copy Excel file to Array - starting on row 2 to avoid the column headers from being in array[1]
Global $excelArray = _ExcelReadSheetToArray($openExcelFile,2)

; $i is the array's rows and it will increase by 1 each time the loop completes until the array ends
For $i = 0 to UBound($excelArray)
    ; If the Enrollment Status Column[16] is Null or the Graduated Column[17] is 'Y' then delete the row
    If $excelArray[$i][16] = "" Then
    _ArrayDelete($excelArray, $i)
    ElseIf $excelArray[$i][17] = "Y" Then
    _ArrayDelete($excelArray, $i)
    EndIf
Next

; Display Array - Delete after finishing the Array section
_ArrayDisplay($excelArray, "Display File's Array")

; Close Excel file without saving and with and Excel message alerts disabled
_ExcelBookClose($openExcelFile,0,0)

Exit

[BrewManNH]

Try looping through the array in reverse, use this.
 

For $i = UBound($excelArray) - 1 to 0 Step -1

[Althalus]

Thank you BrewManNH, that worked.  Would you be able to tell me why going backwards through the array works, instead of forwards?

[BrewManNH]

Once you delete 1 row from your array, every row after it will be decreased by 1, so you're not deleting the rows you think you are.

• Delete row 3
• Row 4 is now the new row 3
• Delete row 4, which is the former row 5
• You've now deleted 5 instead of 4 and 4 is still there in a new slot.

[Althalus]

Makes sense, thank you again.

[Althalus]

I thought about opening another request for this but as it is still on the _ArrayDelete function I decided it might be best to continue on this post.  I have finished my program, except for a section where I am trying to delete rows for a 2D array if one of its columns matches one of the values in a 1D array.  I have removed the rest of the program after the error, as it is working correctly.  Lines 56 - 62 is my latest attempt to delete it with a nested for loop, it does not return errors but the rows are not deleted in the array.

expandcollapse popup

#include <File.au3>
#include <Excel.au3>
#include <Array.au3>

;-----------------------------------------------------------------------------------------------------------------------------
; Open/Read/Manipulate the Excel File
;-----------------------------------------------------------------------------------------------------------------------------
Global $excelFile = FileOpenDialog("Open File That Is From The Clearinghouse", @ScriptDir & "\", "Excel files (*.xlsx)", 1)

; Open User Specified Excel file
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $openExcelFile = _ExcelBookOpen($excelFile,1,True)

; Message to inform user something is happening...
MsgBox(0, "Reading File", "Reading File. Please wait.", 5)

; Copy Excel file to Array - starting on row 2 to avoid the column headers from being in array[1]
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $excelArray = _ExcelReadSheetToArray($openExcelFile,2)

; Close Excel file without saving and with and Excel message alerts disabled
;--------------------------------------------------------------------------------------------------------------------------------------------
_ExcelBookClose($openExcelFile,0,0)

; If the Enrollment Status Column[16] is Null or the Graduated Column[17] is 'Y' then add the unique Idenifier to $aArray
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $aArray[UBound($excelArray)]

For $i = UBound($excelArray) - 1 to 0 Step -1
    If $excelArray[$i][16] <> " " Then
    _ArrayAdd($aArray, $excelArray[$i][6])
    ElseIf $excelArray[$i][17] = "Y" Then
    _ArrayAdd($aArray, $excelArray[$i][6])
    EndIf
Next

_ArrayDisplay($aArray, "$aArray")

; Bring over the unique values in $aArray to $uniqueIdArray and delete row 0(which will be replace with an empty row, which cannot be gotton rid of) and any empty rows.
;--------------------------------------------------------------------------------------------------------------------------------------------
Global $aUniqueIdArray = _ArrayUnique($aArray)

For $i = UBound($aUniqueIdArray) - 1 to 0 Step -1
    If $i = 0 Then
    _ArrayDelete($aUniqueIdArray, $i)
    ElseIf $aUniqueIdArray[$i] = " " Then
    _ArrayDelete($aUniqueIdArray, $i)
    EndIf
Next

_ArrayDisplay($aUniqueIdArray, "$aUniqueIdArray")


; Delete rows from $excelArray, which has the Unique Ids that are stored in $uniqueIdArray
;--------------------------------------------------------------------------------------------------------------------------------------------
For $i = UBound($excelArray,0) - 1 to 0 Step -1
    For $x = UBound($aUniqueIdArray) - 1 to 0 Step -1
        If $aUniqueIdArray[$x] == $excelArray[$i][6] Then
        _ArrayDelete($excelArray, $i)
        EndIf
    Next
Next

#cs
; search Banner id's for ADP id's
For $i = UBound($excelArray,0) - 1 to 0 Step -1
    ; if not in Banner then add to array
    If _ArraySearch($excelArray[$i][6], $aUniqueIdArray[$i]) = -1 Then
    _ArrayDelete($excelArray, $i)
    EndIf
Next
#ce

_ArrayDisplay($excelArray, "$excelArray")

[BrewManNH]

Don't use UBound with 0 in the Dimension parameter in your $I loop, that only gives you the array's subitems ("columns"), and then you're using it in the $item ("row") location in the search routine. Use the default for Ubound, that gives you the row count.

[jdelaney]

Ubound Dimension paramater note from helpfile: [optional] Which dimension of a multi-dimensioned array to report the size of. Default is 1, which is the first dimension. If this parameter is 0, the number of subscripts in the array is returned.

Subscripts are the count of brackets on the array.

Small example:

Local $array1[10][8][6][4][2]
ConsoleWrite("There are [" & UBound($array1,0) & "] subscripts in the array" & @CRLF)
For $i = 1 To UBound($array1,0)
    ConsoleWrite("Dimension [" & $i & "] includes a ubound of [" & UBound($array1,$i) & "]" & @CRLF)
Next

output:

There are [5] subscripts in the array
Dimension [1] includes a ubound of [10]
Dimension [2] includes a ubound of [8]
Dimension [3] includes a ubound of [6]
Dimension [4] includes a ubound of [4]
Dimension [5] includes a ubound of [2]

Edited March 7, 2014 by jdelaney

[Althalus]

I had tried that originally, it returns an error of:

Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

If $aUniqueIdArray[$x] == $excelArray[$i][6] Then

If $aUniqueIdArray[$x] == ^ ERROR

 

For $i = UBound($excelArray) - 1 to 0 Step -1
    For $x = UBound($aUniqueIdArray) - 1 to 0 Step -1
        If $aUniqueIdArray[$x] == $excelArray[$i][6] Then
        _ArrayDelete($excelArray, $i)
        EndIf
    Next
Next

 

I have the search Array section commented off, as it did not work for me to delete either.

[jdelaney]

Looks fine to me.

You don't need to reverse step through the inner loop.

Try adding an _arraydisplay of the $excelArray, just prior to the outer loop.  Make sure it's what you expect it to be.

[BrewManNH]

You need to exit the $x loop (ExitLoop) after you've deleted the array row, otherwise you'll be searching the wrong row again.

[Althalus]

I think the ExitLoop did it.  Thank you both very much, without you I do not believe I could have finished this project.

; Delete rows from $excelArray, which has the Unique Ids that are stored in $uniqueIdArray
;--------------------------------------------------------------------------------------------------------------------------------------------
For $i = UBound($excelArray,1) - 1 to 0 Step -1
    For $x = UBound($aUniqueIdArray) - 1 to 0 Step -1
        If $aUniqueIdArray[$x] == $excelArray[$i][6] Then
        _ArrayDelete($excelArray, $i)
        ExitLoop
        EndIf
    Next
Next