Array Search Failing To Find Match

[BatMan22]

I wrote a function to find any duplicate dates in a excel column by using ArraySearch but ArraySearch doesn't yield anything even when I paste in duplicate data.. I can SEE the duplicate data in the _ArrayDisplay function... What am I doing wrong? Eventually I just want this function to delete any column with duplicate dates (dates that have already been seen). Is there an easier way to do this?

 

Func CheckForDups()
    $dateandtimerow = "2"
    $LastCol = "L"
    local $rangetoread = "B" & $dateandtimerow & ":" & $lastcol & $dateandtimerow
    ConsoleWrite("TheRangeWereLooking at dates and times are: " & $rangetoread & @CRLF)
    local $DATARRAY = _Excel_RangeRead($oname, "Edit Here", $rangetoread)
    _ArrayDisplay($DATARRAY)
    For $i = 0 to UBound($DATARRAY, 2)-1
        local $found = _ArraySearch($DATARRAY, $DATARRAY[0][$i])
        ConsoleWrite("Found at : " & $found & @CRLF)
    Next
    Exit
EndFunc

 

DuplicateDates.xlsx

[BrewManNH]

It is finding matches, all of your data is in one row, _ArraySearch tells you what row your match was found in, not the subitem of that row.

[Subz]

You can search the row, see $bRow parameter, however it would be better to use _ArrayFindAll as this will find all instances, example:

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

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\DuplicateDates.xlsx")

CheckForDups()

Func CheckForDups()
    Local $aSearch
    $dateandtimerow = "2"
    $LastCol = "L"
    local $rangetoread = "B" & $dateandtimerow & ":" & $lastcol & $dateandtimerow
    ConsoleWrite("TheRangeWereLooking at dates and times are: " & $rangetoread & @CRLF)
    local $aDataArray = _Excel_RangeRead($oWorkbook, "Edit Here", $rangetoread)
    _ArrayDisplay($aDataArray)
    For $i = UBound($aDataArray, 2) - 1 To 0 Step - 1
        $aSearch = _ArrayFindAll($aDataArray, $aDataArray[0][$i], 0, 0, 0, 0, 0, True)
        If UBound($aSearch) - 1 = 0 Then ContinueLoop
        For $j = UBound($aSearch) - 1 To 1 Step - 1
            _ArrayColDelete($aDataArray, $aSearch[$j])
            $i -= 1
        Next
    Next
    _ArrayDisplay($aDataArray)
EndFunc

 

[BatMan22]

Of course, I even used the F1 function.. I'm still struggling with 2D arrays and my understanding in manipulating/using them. Thanks guys.

[BatMan22]

@Subz, if you don't mind me asking a part two to my question.. I tried to use/modify what you wrote to delete the column in the excel with the duplicate date in it.. and it deletes a lot more then just those rows... I switched back to ArraySearch from the ArrayFindAll because I couldn't figure out how to use the output from the FindAll to do what I needed (delete one of the duplicate rows). 

 

For $i = UBound($aDateArray, 2) - 1 To 0 Step -1
        $aSearch = _ArraySearch($aDateArray, $aDateArray[0][$i], 0, Default, Default, Default, Default, Default, True)
        If $aSearch <> $aDateArray[0][$i] Then ; If it finds anything BUT itself then delete THAT row.. Problem seems to originating here..
            _Excel_RangeDelete($oname.ActiveSheet, _Excel_ColumnToLetter($aSearch) & ":" & _Excel_ColumnToLetter($aSearch) , Default, 2)
        EndIf
        ConsoleWrite("FOUND IS: " & $aSearch & @CRLF)
    Next
    Exit

 

[Subz]

Here is a working example for both _ArraySearch and _ArrayFindAll, just uncomment/comment one or the other, I find that you need to keep your array in sync with your spreadsheet when searching, _ArraySearch will search one by one, whereas _ArrayFindAll will find all instances and delete all but the first item found.  Hope that makes sense.

expandcollapse popup

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

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\DuplicateDates.xlsx")

CheckForDups()

Func CheckForDups()
    Local $aSearch, $iFirstColumn
    Local $iDateandTimeRow = "2"
    Local $sFirstColumn = "B"
    Local $iFirstColumn = _Excel_ColumnToNumber($sFirstColumn)
    Local $sLastColumn = "L"
    local $rangetoread = $sFirstColumn & $iDateandTimeRow & ":" & $sLastColumn & $iDateandTimeRow
    ConsoleWrite("TheRangeWereLooking at dates and times are: " & $rangetoread & @CRLF)
    local $aDateArray = _Excel_RangeRead($oWorkbook, "Edit Here", $rangetoread)
    _ArrayDisplay($aDateArray)
    For $i = 0 To UBound($aDateArray, 2) - 1
        $iSearch = _ArraySearch($aDateArray, $aDateArray[0][$i], $i + 1, 0, 0, 0, 1, -1, True)
        If $iSearch = -1 Then ContinueLoop
        ConsoleWrite($aDateArray[0][$i] & " - " & $iSearch & " : " & _Excel_ColumnToLetter($iSearch + $iFirstColumn) & ":" & _Excel_ColumnToLetter($iSearch + $iFirstColumn) & @CRLF)
        _Excel_RangeDelete($oWorkbook.ActiveSheet, _Excel_ColumnToLetter($iSearch + $iFirstColumn) & ":" & _Excel_ColumnToLetter($iSearch + $iFirstColumn), Default, 2)
        _ArrayColDelete($aDateArray, $iSearch)
        $i -= 1
    Next

;~     For $i = UBound($aDateArray, 2) - 1 To 0 Step - 1
;~         $aSearch = _ArrayFindAll($aDateArray, $aDateArray[0][$i], 0, 0, 0, 0, 0, True)
;~         If UBound($aSearch) - 1 = 0 Then ContinueLoop
;~         For $j = UBound($aSearch) - 1 To 1 Step - 1
;~             _ArrayColDelete($aDateArray, $aSearch[$j])
;~          $iFirstColumn = _Excel_ColumnToNumber($sFirstColumn)
;~          _Excel_RangeDelete($oWorkbook.ActiveSheet, _Excel_ColumnToLetter($aSearch[$j] + $iFirstColumn) & ":" & _Excel_ColumnToLetter($aSearch[$j] + $iFirstColumn), Default, 2)
;~             $i -= 1
;~         Next
;~     Next
    _ArrayDisplay($aDateArray)
EndFunc

 

[BatMan22]

Thanks dude, you rock. 

[BatMan22]

13 hours ago, Subz said:

For $j = UBound($aSearch) - 1 To 1 Step - 1

THAT'S what I didn't understand/didn't know how to deal with.. With that line you're skipping the matching of itself right? That's why it's Ubound-1 to 1 instead of Ubound-1 to 0?

[Subz]

That is correct the $aSearch[0] is the first item found, so we only delete the columns from x to 1.