Excel flow not working

[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.

[Highboy]

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 😊

[Hana123TEI]

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 ...

[VJR]

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

[Hana123TEI]

@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 

[VJR]

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.

[VJR]

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.

[Hana123TEI]

@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 ...

[VJR]

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

[Hana123TEI]

@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....

[VJR]

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

[Hana123TEI]

@VJR I tried through breakpoint , what I feel is in my original excel sheet the row start from 12 and in sheet 1 it start from row 2...so i changed row count 12 but it dint give the expected results.

[VJR]

You can maintain two different Count variables and pass it while accessing the appropriate sheet.

[Hana123TEI]

@VJR Thanks for your support , somehow I was not able to make it hence doing it the other way...