Unanswered

Excel flow not working

(0) Share

Report

Posted on by Hana123TEI

I have made a flow to run and search a data from 1 column to other sheet and if the match found then paste the data from sheet 1 to sheet 2 same row where the match is found.

my flow is running but not giving the expected result.

any suggestions will highly appreciated.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (13)

Answers (0)

Sort by

Highboy 1,187 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

What result do you get?
Would you please share the relevant part of the code?
It would make it a bit easier to help you 😊

Was this reply helpful? Yes No
Hana123TEI 49 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

The Process I map was if it found a match then it will display message ok , the flow runs and stop but it does not display match.
2) Ones it found match then read the data from than match other column and paste in other excel sheet where the same match is there , but unfortunately i am not able to make the flow for it.

Hope this is Ok ...

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

@Hana123TEI , can you share how your values in Excel look like and what options have you given in the Find action in your flow.

Was this reply helpful? Yes No
Hana123TEI 49 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR attached is the flow ,this flow has to search the value from sheet 1 column starting from B2 and ending till B2 and every time when searching first value need to find it on other sheet column and if match found need to write the value from sheet 1 column C

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

You can also search using a vlookup formula which will be faster than finding every value and interacting with both sheets.

Add this formula in the cell in column B against which you want to evaluate the value

=VLOOKUP(A2,Sheet1!$B$2:$C$7,2,FALSE)

The parts you need to change are:

- Instead of A2 if you are searching for value of cell A3 then write A3 in the formula while using "Write to Excel".

- Secondly, I have used B$2:$C$7 because my data is in this range on Sheet1. So change accordingly as per your data. This has to be done only once.

You could also copy/paste the formula to all the cells in the column B all at once, but I see that you do not want to search for particular cells that are not in green.

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

If you want to copy/paste the formula all at once in col B then

- first write the same formula in B2 as shown above

- then ctrl c on B2

- Ctrl V it from B3 to B10 (eg; if 10 is your last row)

Thats it. The copy paste will automatically update the formula for all rows.

Was this reply helpful? Yes No
Hana123TEI 49 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR Thanks for Quick reply, but I don't want to manually put the formula, This is Just sample which i am looking for actually the excel will be different files which different style and I do not want to change the format of the file, hence want to do it by Power automate , .......Is there any other way around ...

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!
If you would like to still go with Loops then below is how it would look like.

The Loop:

Sheet 1 and Sheet 2 before running the process:

Sheet 2 after running the process:

Copy-Paste the below code in a blank Flow editor, make the necessary changes like Excel File path etc and run the Flow.

Hope this should get you going.

If so you could close the thread.

Good day!

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''c:\\test\\ExcelFiles.xlsx''' Visible: True ReadOnly: True Instance=> ExcelInstance Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet2''' Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow SET RowCnt TO 2 LOOP WHILE (RowCnt) < (FirstFreeRow) Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet2''' Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: $'''A''' StartRow: RowCnt ReadAsText: False CellValue=> Sheet2Value Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1''' Excel.FindAndReplace.FindAll Instance: ExcelInstance TextToFind: Sheet2Value MatchCase: False MatchEntireCellContents: False SearchBy: Excel.SearchOrder.Rows Cells=> ValueFoundInCell IF IsNotEmpty(ValueFoundInCell) THEN Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: $'''C''' StartRow: ValueFoundInCell[0][1] ReadAsText: False CellValue=> ValueToWrite Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet2''' Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: ValueToWrite Column: $'''B''' Row: RowCnt END Variables.IncreaseVariable Value: RowCnt IncrementValue: 1 IncreasedValue=> RowCnt END

Was this reply helpful? Yes No
Hana123TEI 49 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR So far I have been tested this flow this flow is working but I wonder although the match is not there it is writing the Result ...I tried to solve this but unable to do that....

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Can you point a breakpoint on line #12 and step through one line at a time and see where it is going wrong.

Was this reply helpful? Yes No