Excel Automation

[WindIt]

Hi, 

I have a set of excel docs 1& 2

1) In Excel 1 i have a fixed column called dimensionId that contains list of dimensionid's with in between empty cells.

Here, For each dimesionId, i need to check corresponding columns of L,M,N ,O(senior, mid, junior, student) & check If they contain letter Y (Open excel 2) and if empty ignore,check next.

I need to loop through the entire column of dimensionId one by one, please help me proceed with the logic.

2) If letter Y exists, open excel 2 and check if same dimesionId exists in column A.

New to autoit excel automation, Help me with logic to execute this.

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "D:\yta\Trial concept dimensions list.xlsx")

Local $read1 = _Excel_RangeRead ($oWorkbook,Default,"C3")

MsgBox(0,"",$read1)

Attached exce 1 doc.

 

 

exl1.xlsx exl2.xlsx

[dmob]

Read both sheets into arrays and use _ArraySearch or _ArrayFindAll with the Regex search parameter.

Edited September 14, 2020 by dmob

[WindIt]

8 hours ago, dmob said:

Read both sheets into arrays and use _ArraySearch or _ArrayFindAll with the Regex search parameter.

Can you share some sample example please.

[dmob]

1 hour ago, WindIt said:

Can you share some sample example please.

You have already done half of it...

#include <Excel.au3>

 Example()

 Func _Example()
    Local $oExcel = _Excel_Open()
    Local $oWorkbook1 = _Excel_BookOpen($oExcel, "D:\yta\exl1.xlsx")
    ; check for error
    
    Local $aRead1 = _Excel_RangeRead($oWorkbook1, Default, "C3") ; returns an array of Col C3
    ; check for error

    Local $oWorkbook2 = _Excel_BookOpen($oExcel, "D:\yta\exl2.xlsx")
    ; check for error
    
    Local $aRead2 = _Excel_RangeRead($oWorkbook2, Default) ; returns an array of entire sheet 
    ; check for error
    
    ; now you have both sheets in $aRead1 & $aRead2
    ; now do your comparison. See _ArraySearch & _ArrayFindAll
 EndFunc

 

[Subz]

You would have to change $aRead1 to be an array first:

Local $aRead1 = _Excel_RangeRead($oWorkbook1, Default, $oWorkbook1.ActiveSheet.Usedrange.Columns("C:N"), 2)

However personally I would just use a formula in Excel for example, use this formula in Q column to determine if L, M, N match exl2.xlsx X, Y, Z columns:

=IFNA(IF(OR(VLOOKUP(C6,[exl2.xlsx]Sheet1!$A$1:$AH$331,24,FALSE)=L6,VLOOKUP(C6,[exl2.xlsx]Sheet1!$A$1:$AH$331,25,FALSE)=M6,VLOOKUP(C6,[exl2.xlsx]Sheet1!$A$1:$AH$331,26,FALSE)=N6),"Found - Same Values","Found - Different Values"),"Not Found")

 

[WindIt]

Thank You guys, very helpful inputs.