cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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 AColumn BColumn CColumn DColumn EColumn F
Apple4Orange5Pear2

 

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.

 

Agetro10_0-1623813120351.png

 

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.

 

 

3 REPLIES 3
ApexOrigin
Advocate II
Advocate II

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
Helper I
Helper I

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
Not applicable

Hi @ApexOrigin and @ShanKM ,

 

Thanks for the suggestions!

 

They are very helpful. Let me try them out!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,644)