PAD ExcelData (Datatable) match with another column in similar Datatable

[rifij]

working on a Flow and am getting stuck on the next action I'm wanting to take. Essentially, the flow in PAD (Power Automate Desktop) goes to a specific folder and looks for a file. From there, it moves the file to a destination folder. From there, it launches an Excel instance (via Launch Excel) and opens the file in the folder specified. After a few manipulations of data and different variables, the flow does a read from excel where the data items from the excel instance and stores it as ExcelData. From there (ExcelData) I used the find or replace (Find only in this scenario) in data table to look for items within the ExcelData for a specific text (In this case text is FP3000) from a specific column and stores the matched cells as DataTableMatches. Ideally, the next thing I'm looking to do is do a match action of FP3000 with items listed in the "Fleet Deal Drop Ship Dealer" (In this ex, matching FP3000 to corresponding DS that starts with 32, 01, 39, 66, etc.). Ideally, I'd like to set the match to occur when FP3000 (dependent value) is there and the Column prefix #s start with numbered prefixes. Unfortunately, after looking at a variety of ways I have not been successful and wanted to see if anyone has come across this and how they solved it.

[VishnuReddy1997]

Hi @rifij ,

Last part i didnt get it can you ellabroate it.

And if you have any screenshots of the flow please share and write what you want or share the screenshots of PDD.

Regards,

Vishnu Reddy

[VishnuReddy1997]

Hi @rifij ,

After so much time as per my understanding i have written VBscript for this.

The VBScript provided opens an Excel file and scans through each row in the first sheet. When it detects "FP3000" in a cell of column A, it replaces only that substring with the corresponding value from column G. The script saves the changes, closes Excel.

Please Copy the below code directly to your flow after writing the data table to excel and closing excel as a save document.

SET Excel_Filepath TO $'''C:\\Users\\Desktop\\Date_Format_Test.xlsx'''
@@copilotGeneratedAction: 'False'
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Option Explicit

Dim objExcel, objWorkbook, objWorksheet
Dim iRow, lastRow

\' Excel file path
Const filePath = \"%Excel_Filepath%\"

\' Create Excel object
Set objExcel = CreateObject(\"Excel.Application\")

\' Disable alerts to prevent confirmation prompts
objExcel.DisplayAlerts = False

\' Open the workbook
Set objWorkbook = objExcel.Workbooks.Open(filePath)

\' Set the first worksheet as the active sheet
Set objWorksheet = objWorkbook.Worksheets(1)

\' Find the last row with data in column A
lastRow = objWorksheet.Cells(objWorksheet.Rows.Count, \"A\").End(-4162).Row \' -4162 corresponds to xlUp

\' Loop through each row in column A
For iRow = 1 To lastRow
    \' Check if the cell in column A contains \"FP3000\" (case-insensitive)
    If InStr(1, objWorksheet.Cells(iRow, 1).Value, \"FP3000\", vbTextCompare) > 0 Then
        \' Replace \"FP3000\" with the corresponding value from column G
        Dim originalValue, replacedValue
        originalValue = objWorksheet.Cells(iRow, 1).Value
        replacedValue = ReplaceWithG(originalValue, objWorksheet.Cells(iRow, 1).Offset(0, 6).Value)
        objWorksheet.Cells(iRow, 1).Value = replacedValue
    End If
Next

\' Save and close the workbook
objWorkbook.Save
objWorkbook.Close

\' Quit Excel application
objExcel.Quit

\' Release the objects
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing

WScript.Echo \"Replacement completed successfully.\"

Function ReplaceWithG(originalValue, replacement)
    Dim parts, part, i
    parts = Split(originalValue, \",\")
    For i = LBound(parts) To UBound(parts)
        If InStr(1, parts(i), \"FP3000\", vbTextCompare) > 0 Then
            parts(i) = replacement
            Exit For
        End If
    Next
    ReplaceWithG = Join(parts, \",\")
End Function''' ScriptOutput=> VBScriptOutput3

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

Thanks & Regards

Vishnu Reddy

[pradeepvarma]

For complex calculations in excel using PAD, its recommended to use DB activities which are there in PAD.
Using that you can connect to excel easily and with a single command, you may filter or do complex activities.

Try below query by tweaking it to your need and let me know if that works:
SELECT Excel2.*
FROM Excel2
INNER JOIN Excel1 ON Excel2.ColumnName2 = Excel1.ColumnName1