Hi @Rpa123
You can give try with the below way to design this flow
This is designed using common logic
1. Reading the data excel sheet
2. Reading Causes excel sheet
3. Looping through each row in data worksheet
4. Lookup the cause ID from data worksheet to Causes worksheet, if value found update the data worksheet Causes column
5. Finally write the updated data datatable to excel under data worksheet.
Copy paste the below script for that
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''D:\\Temp\\PAD_Question.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Data'''
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> DataFreeColumn FirstFreeRow=> DataFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 3 EndColumn: DataFreeColumn - 1 EndRow: DataFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> DataExcel
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Causes'''
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> CauseFreeColumn FirstFreeRow=> CauseFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: CauseFreeColumn - 1 EndRow: CauseFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> CauseExcelData
SET rowindex TO 0
LOOP FOREACH row IN DataExcel
Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: CauseExcelData AllMatches: False ValueToFind: $'''id=\"%row['Cause/Action Id number']%\"''' MatchCase: False MatchEntireCellContents: True ColumnNameOrIndex: $'''CauseId''' DataTableMatches=> DataTableMatches
IF DataTableMatches.RowsCount <> 0 THEN
Text.ToNumber Text: DataTableMatches[0]['Row'] Number=> rowNumber
Variables.ModifyDataTableItem DataTable: DataExcel ColumnNameOrIndex: $'''Causes''' RowIndex: rowindex Value: CauseExcelData[rowNumber]['Causes']
END
Variables.IncreaseVariable Value: rowindex IncrementValue: 1
END
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Data'''
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: DataExcel Column: $'''A''' Row: 4
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
Make sure to change the file path of excel used here in launch excel action before running the script. The output looks like this:
Note: may be u need to make changes in flow if you need to read file from A2 cell instead of A4 IN Data worksheet
