Unanswered

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

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

Sort by

ApexOrigin 150 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ShanKM 45 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

Hi @ApexOrigin and @ShanKM ,

Thanks for the suggestions!

They are very helpful. Let me try them out!

Was this reply helpful? Yes No