Excel: Identifying a Cell with Certain Content and Writing Content of Cell Next to it

[Anonymous]

Hi all,

I am trying to get PAD to identify a Excel cell with certain content, and then getting it to write the content of the cell next to the identified cell.

For example, I want to get PAD to identify the cell with the word "Apple" and then get it to write "4" to an excel sheet. The word "Apple" may appear in Columns A to E, but the content I want to write will also be in the cell on the right side of the cell containing "Apple". 

Column A	Column B	Column C	Column D	Column E	Column F
Apple	4	Orange	5	Pear	2

Currently I saved each Column under one ExcelData variable. That means for Column A to F, I have 6 ExcelData variables.

I then get PAD to run through each ExcelData variable looking for "Apple" using "For Each" loop as per the snapshot below. I also set a Variable, so that PAD can run through each row, and write the content of the cell beside "Apple" cell.

Even though this works, I had to set up many "For Each" loops. In addition, I am not just looking for "Apple". There are another 50 others which I would need to identify, so that "For Each" loop to be set up would be huge and generally time consuming to run through the flow.

Does anyone have any suggestions on how I can do this better and more efficiently?

Thanks in advance.

[ApexOrigin]

I do this sort of thing all the time.

I would put the 50 or so items into a list variable, use the count option to get the number of items in the list, then run a loop for that many times, and the loop would run your existing loop 50 times, except instead of hardcoding "apple" into the loop, use the variable with the current list item.

You could also put the list into a spreadsheet, and read the items from a spreadsheet if that's easier than entering the items directly into a list variable. 

It seems like nested sets of loops are all I do because it comes up so often.

[ShanKM]

Hi @Anonymous ,

Try the excel as a database method as shown in https://lazybots.blogspot.com/2021/06/excel-as-database-in-power-automate.html

using this method you can get the column list first, then iterate on the column list and perform the update operation as mentioned in the video

by this method you can reduce the number of foreach loops and execution time.

[Anonymous]

Hi @ApexOrigin and @ShanKM ,

Thanks for the suggestions!

They are very helpful. Let me try them out!