emulating excel Index and Match function

[quadcom]

Hi, I'm new to the powerautomate desktop. Recently, I built and ran a flow emulating Index and Match functions in Excel; I had problems with no data on the output Excel; below is the picture of the flow that I've altered from ChatGPT as a guide, hope someone can help me.

[Sayan]

Hey @quadcom 

After analysing the Workflow I can see that you are writing the on the ExcelSheet which have instance ExcelInstance.

Is this correct file?

------------------------------------------------------------------------------------------------------------------------------

If my suggestion helps you, please gives it a Thumbs up and Marks as a Solution. Which motivates to solve more problems.

Thanks,

Sayan Patra | RPA Developer

[VishnuReddy1997]

Hi @quadcom ,

Please find the solution.There no such index as columnindex.

Code:

Please copy the below code to your flow.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Desktop\\Input1.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Desktop\\Input2.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance2
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn1 FirstFreeRow=> FirstFreeRow1
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: 1 StartRow: 1 EndColumn: FirstFreeColumn1 EndRow: FirstFreeRow1 ReadAsText: False FirstLineIsHeader: False RangeValue=> SimpleData2
Excel.GetFirstFreeColumnRow Instance: ExcelInstance2 FirstFreeColumn=> FirstFreeColumn2 FirstFreeRow=> FirstFreeRow2
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance2 StartColumn: 1 StartRow: 1 EndColumn: FirstFreeColumn2 EndRow: FirstFreeRow2 ReadAsText: False FirstLineIsHeader: False RangeValue=> SimpleData1
SET SimpleDataHeader2 TO SimpleData2[0]
SET SimpleDataHeader1 TO SimpleData1[0]
SET SimpleData2RepColoumIndex TO $'''indexOf(%SimpleDataHeader2%,\"Rep\")'''
SET SimpleData1RegionColoumIndex TO $'''indexOf(%SimpleDataHeader1%,\"Region\")'''
SET SimpleData1RepColoumIndex TO $'''indexOf(%SimpleDataHeader1%,\"Rep\")'''
SET LoopIndex TO 0
LOOP FOREACH CurrentRow1 IN SimpleData1
    IF LoopIndex > 0 THEN
    END
    Variables.IncreaseVariable Value: LoopIndex IncrementValue: 1
END
SET LoopIndex TO 0
LOOP FOREACH CurrentRow2 IN SimpleData2
    IF LoopIndex > 0 THEN
    END
    Variables.IncreaseVariable Value: LoopIndex IncrementValue: 1
END

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[quadcom]

Yes

[quadcom]

I'll try first

[quadcom]

sorry to disturb, but there is still no data on the current row function, only columns

[VishnuReddy1997]

Hi @quadcom ,

Can you explain what is your requirement in detail.

Regards,

Vishnu Reddy

[quadcom]

to do a vlookup or index and match functions between two excel files, automated using PowerAutomationDesktop

[VishnuReddy1997]

Hi @quadcom ,

Can you share the Headers of both the excels and can you tell me which columns need to be comparared and which column need to be pasted when it matches.

Regards,

Vishnu Reddy

[quadcom]

reference excel headers is in black, target excel headers is in blue, 

Rep is the columns to compare.

Region is the columns to paste to.

[VishnuReddy1997]

Hi @quadcom ,

Can you try these with Vbscript.Please paste the below Vbscript in Run VBScript action.

VBscript :

Dim xlApp, xlInputWorkbook, xlOutputWorkbook, xlInputSheet, xlOutputSheet
Dim lastRowInput, lastRowOutput, i, j
Dim inputKeyA, inputValueB, outputKeyC

' Create Excel application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False ' Set to True if you want to see the Excel windows

' Open the input and output workbooks
Set xlInputWorkbook = xlApp.Workbooks.Open("C:\Path\To\InputExcel.xlsx") ' Replace with your input file path
Set xlOutputWorkbook = xlApp.Workbooks.Open("C:\Path\To\OutputExcel.xlsx") ' Replace with your output file path

' Assuming data is on the first sheet of both workbooks
Set xlInputSheet = xlInputWorkbook.Sheets(1)
Set xlOutputSheet = xlOutputWorkbook.Sheets(1)

' Find the last row with data in the input and output sheets
lastRowInput = xlInputSheet.Cells(xlInputSheet.Rows.Count, "A").End(-4162).Row ' -4162 is xlUp
lastRowOutput = xlOutputSheet.Cells(xlOutputSheet.Rows.Count, "C").End(-4162).Row

' Loop through the rows of the input sheet
For i = 2 To lastRowInput ' Start from 2 to skip header
    inputKeyA = xlInputSheet.Cells(i, 1).Value ' Column A
    inputValueB = xlInputSheet.Cells(i, 2).Value ' Column B

    ' Loop through the rows of the output sheet
    For j = 2 To lastRowOutput ' Start from 2 to skip header
        outputKeyC = xlOutputSheet.Cells(j, 3).Value ' Column C

        ' Check if columns match
        If inputKeyA = outputKeyC Then
            ' Place the value from input column B into output column B
            xlOutputSheet.Cells(j, 2).Value = inputValueB
            Exit For
        End If
    Next
Next

' Save and close the workbooks
xlOutputWorkbook.Save
xlInputWorkbook.Close False
xlOutputWorkbook.Close

' Quit Excel application
xlApp.Quit

' Clean up
Set xlInputSheet = Nothing
Set xlOutputSheet = Nothing
Set xlInputWorkbook = Nothing
Set xlOutputWorkbook = Nothing
Set xlApp = Nothing

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy