Comparing of values in array to excel value and with judgement

[HBJR]

I seek your help how I can compare the value in my array to have judgement. if the value in array is in the value in excel it will judge Good in Col1.

Please help me. thank you

[ioa747]

example

#include <array.au3>
#include <Excel.au3>

Global $oExcel, $oWorkbook, $aSplit

$oExcel = _Excel_Open(False)

If Not FileExists(@ScriptDir & "\test1.xlsx") Then
    $aSplit = StringSplit('title;SSW001;SSW002;SSW007;SSW004;SSW005;SSW006;SSW003;SSW008;SSW009;SSW016;SSW011;SSW012;SSW013;SSW014;SSW015;SSW010', ";", 2)
    _ArrayColInsert($aSplit, 1)
    $oWorkbook = _Excel_BookNew($oExcel)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aSplit, "A1")
    _Excel_BookSaveAs($oWorkbook, @ScriptDir & "\test1.xlsx", Default)
    _Excel_BookClose($oWorkbook, False)
    Sleep(1000)
EndIf

$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test1.xlsx", True, False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening test1.xlsx")
Local $aExcel = _Excel_RangeRead($oWorkbook, 1)
_Excel_BookClose($oWorkbook, False)
;~ _ArrayDisplay($aExcel, "$aExcel")

$aSplit = StringSplit('SSW001;SSW002;SSW003;SSW004;SSW005;SSW006;SSW007;SSW008;SSW009;SSW010;SSW011;SSW012;SSW013;SSW014;SSW015;SSW016', ";", 2)
_ArrayColInsert($aSplit, 1)

For $i = 0 To UBound($aSplit) - 1
    If $aSplit[$i][0] = $aExcel[$i + 1] Then
        $aSplit[$i][1] = 1
    Else
        $aSplit[$i][1] = 0
    EndIf
Next

_ArrayDisplay($aSplit, "$aSplit")

   to forum

[Nine]

I would think he meant to check if 0004 of SSW001 array is part of the values in Excel row of the same name, 0004 for SSW002, and 0007 in SSW003

Edited January 8, 2024 by Nine

[ioa747]

Part II

expandcollapse popup

#include <array.au3>
#include <Excel.au3>

Test()

Func Test()
    Local $oExcel, $oWorkbook, $aArray

    $oExcel = _Excel_Open(False)

    If Not FileExists(@ScriptDir & "\test2.xlsx") Then
        Local $aArray = [ _
                ["'SSW001", "'FFFB", "'FFFC", "'FFFD", "'FFFE", "'FFFF", "'0000", "'0001", "'0002", "'0003", "'0004", "'0005", "'0006", "'0007", "'0008", "'0009", "'000A"], _
                ["'SSW002", "'FFFB", "'FFFC", "'FFFD", "'FFFE", "'FFFF", "'0000", "'0001", "'0002", "'0003", "'0004", "'0005", "'0006", "'0007", "'0008", "'0009", "'000A"], _
                ["'SSW003", "'FFFB", "'FFFC", "'FFFD", "'FFFE", "'FFFF", "'0000", "'0001", "'0002", "'0003", "'0004", "'0005", "'0006", "'0007", "'0008", "'0009", "'000A"]]

        $oWorkbook = _Excel_BookNew($oExcel)
        _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, "A1")
        _Excel_BookSaveAs($oWorkbook, @ScriptDir & "\test2.xlsx", Default)
        _Excel_BookClose($oWorkbook, False)
        Sleep(1000)
    EndIf

    $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test2.xlsx", True, False)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening test1.xlsx")
    Local $aExcel = _Excel_RangeRead($oWorkbook, 1)
    _Excel_BookClose($oWorkbook, False)
    _Excel_Close($oExcel)
;~ _ArrayDisplay($aExcel, "$aExcel")

    $aArray = StringSplit("SSW001 =0004;SSW002 =0004;SSW003 =0007", ";", 2)
    _ArrayColInsert($aArray, 1)

    Local $aVal
    For $i = 0 To UBound($aArray) - 1
        For $c = 0 To UBound($aExcel, 2) - 1
            $aVal = StringSplit($aArray[$i][0], "=")
            If $aVal[0] = 2 Then
                If $aVal[2] = $aExcel[$i][$c] Then
                    $aArray[$i][1] = _Excel_ColumnToLetter($c + 1) & " : " & $i + 1
                EndIf
            EndIf
        Next
    Next

    _ArrayDisplay($aArray, "$aArray")
EndFunc   ;==>Test

 

[ioa747]

Part III

expandcollapse popup

#include <array.au3>
#include <Excel.au3>

Test()

Func Test()
    Local $oExcel, $oWorkbook, $aArray

    $oExcel = _Excel_Open(False)

    If Not FileExists(@ScriptDir & "\test3.xlsx") Then
        Local $aArray = [ _
                ["SSW001 FFFB, FFFC, FFFD, FFFE, FFFF, 0000, 0001, 0002, 0003, 0004, 0005, 0006, 0007, 0008, 0009, 000A"], _
                ["SSW002 FFFB, FFFC, FFFD, FFFE, FFFF, 0000, 0001, 0002, 0003, 0004, 0005, 0006, 0007, 0008, 0009, 000A"], _
                ["SSW003 FFFB, FFFC, FFFD, FFFE, FFFF, 0000, 0001, 0002, 0003, 0004, 0005, 0006, 0007, 0008, 0009, 000A"]]

        $oWorkbook = _Excel_BookNew($oExcel)
        _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, "A1")
        _Excel_BookSaveAs($oWorkbook, @ScriptDir & "\test3.xlsx", Default)
        _Excel_BookClose($oWorkbook, False)
        Sleep(1000)
    EndIf

    $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test3.xlsx", True, False)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening test1.xlsx")
    Local $aExcel = _Excel_RangeRead($oWorkbook, 1)
    _Excel_BookClose($oWorkbook, False)
    _Excel_Close($oExcel)
;~ _ArrayDisplay($aExcel, "$aExcel")

    $aArray = StringSplit("SSW001 =0004;SSW002 =0004;SSW003 =0007", ";", 2)
    _ArrayColInsert($aArray, 1)

    Local $aVal, $aSplit, $aFirst
    For $i = 0 To UBound($aArray) - 1
        $aVal = StringSplit($aArray[$i][0], "=")
        If $aVal[0] = 2 Then
            $aSplit = StringSplit($aExcel[$i], ", ", 1)
            $aFirst = StringSplit($aSplit[1], " ", 1)
            $aSplit[1] = $aFirst[2]
            ConsoleWrite($aFirst[1] & " ") ;***
            For $z = 1 To $aSplit[0]
                If $aVal[2] = $aSplit[$z] Then
                    ConsoleWrite("[" & $aSplit[$z] & "] ")
                    $aArray[$i][1] = $z
                    ContinueLoop
                EndIf
                ConsoleWrite(" " & $aSplit[$z] & "  ")
            Next
            ConsoleWrite("" & @CRLF)

        EndIf

    Next

    _ArrayDisplay($aArray, "$aArray")
EndFunc   ;==>Test

 

[HBJR]

ioa747 Good day

I appreciate your help and I lucky that you help me. I will go to Part III. it is suitable that I'm asking for. Thank you so much and have a nice day.