Hi @jthacker
Try this flow
Here I have used the below file
it writes the output into same file in sheet2
Good thing is that it work when you add one more store let's say Store 6
Take this as an input sheet
After running bot, you will get like this
Copy the below code and paste in power automate desktop to get the above code
SET ExcelFile TO $'''C:\\Users\\User1\\Desktop\\Temp\\sample_file.xlsx'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: ExcelFile Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: True FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
SET FinalDT TO { ^ExcelData.Columns }
SET ColsList TO ExcelData.Columns
Variables.RemoveItemFromList.RemoveItemFromListByIndex ItemIndex: 0 List: ColsList
Variables.CreateNewList List=> TempList
LOOP FOREACH row IN ExcelData
LOOP index FROM 1 TO ColsList.Count STEP 1
LOOP LoopIndex FROM 1 TO ExcelData.Columns.Count STEP 1
Variables.AddItemToList Item: $'''%''%''' List: TempList
END
SET TempList[0] TO row['Fruits']
SET TempList[index] TO row[index]
SET FinalDT TO FinalDT + TempList
Variables.ClearList List: TempList
END
END
SET emptydt TO { ^ExcelData.Columns, ExcelData.Columns }
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: ExcelFile Visible: True ReadOnly: False Instance=> ExcelInstance2
Excel.AddWorksheet Instance: ExcelInstance2 Name: $'''Sheet2''' WorksheetPosition: Excel.WorksheetPosition.Last
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance2 Name: $'''Sheet2'''
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: emptydt Column: $'''A''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: FinalDT Column: $'''A''' Row: 2
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance2
Hope this helps !
Mark it as solution if it resolves your query !
