Suggestion on Excel based automation

Help Studio
, ,
1

Hi Team,

I am working on a excel based automation that is currently using Vlook up formula

the process is like this :

Excel 1 (sheet1)is shared that i filter as per the need and now the DT that i have i want to add 2 new column in that let it be set1 and set2.

and its comparison using subacc to check difference in qty if no diff then Qty and Set1 will have same quantity value if not then the value that is there is sheet2 and Set2 will update as match and mismatch value accordingly

could yo guide / help how to automate this

Forum Engagement Daily Reports
2

@manoj2500

If your ultimate goal is to have vlookup in the same file and store the result in the same file, I would suggest to use excel formula.

Use Excel Application Scope inside it use Use Excel file activity then inside it use write cell activity to write the excel formula to vlookup using Write Cell activity.

Then us Fill Range activity to fill the formula till the end.

1 Like
3

You can do it like below, ill attach code for reference. I have made two excels are per your description

Use Read Range – Sheet1 excel and save output as dt1
Read Range – Sheet1 (Excel2.xlsx) and save output asdt2
Drag Write Cell

  • Sheet name: Sheet1
  • Cell: "C2" (Assume column C is “ItemCode”)
  • Value:=VLOOKUP(A2,‘[Excel2.xlsx]Sheet1’!$A:$B,2,FALSE)

Drag AutoFill Range and fill
Source Range**: "C2"
Destination Range**: "C2:C10"you can increase the range as required.

(upload://9BiJlBLWr4NfgGtlPbvNiDBQWxb.zip) (52.5 KB)

image
image393×168 2.6 KB

The image depicts a workflow for processing Excel spreadsheets using sequential activities such as reading ranges, writing cells, and performing operations within an Excel application scope. (Captioned by AI)
The image depicts a workflow for processing Excel spreadsheets using sequential activities such as reading ranges, writing cells, and performing operations within an Excel application scope. (Captioned by AI)337×361 14.8 KB

ExcelVlookup.zip (52.5 KB)

4

@MohammedShabbir its not only row i want to apply this. I want in all row of data that might be 1000 or 10,000 rows @ashokkarale

5

@manoj2500

Fill Range activity will fill the formula from first cell to all the row cells be whatever the number of rows are there in the file.

1 Like
6

you can dynamically increase the range .
in auto fill range , use destination dynamically “C2:C”+DT1.Rows.Count

1 Like
7

@ashokkarale @MohammedShabbir do you know how we use define function that we do in excel

= vlookup(A2,ddd,2,False)
where ddd is data that is there in Col A, B and C we do define fucntion in excel any idea how to do that

8

@manoj2500

Just prepare your formula in the excel and then copy it to assign to a string variable. If your formula have " then escape it with “”

9

@ashokkarale what does that mean prepare your formula in the excel?

10

The formula:
VLOOKUP(A2, '[Excel2.xlsx]Sheet1'!$A:$B, 2, FALSE)

Means:
“Look at the value in A2 → find it in Excel2 (Sheet1, column A) → bring the matching value from column B → only if it’s an exact match.”

Hope this makes your understanding clear.

11

my ask was about this

do you know how we use define function that we do in excel

= vlookup(A2,ddd,2,False)
where ddd is data that is there in Col A, B and C we do define fucntion in excel any idea how to do that

12

Yes, you can use it in UiPath.

After the name is created,Use Write Cell activity
Cell: “C2”
Value:“=VLOOKUP(A2, ddd, 2, FALSE)”

13

I am not clear that you said after the name is created what does that mean @MohammedShabbir

14

Name means ‘ddd’. I meant this which we define.

15

Colud we use Linq query instead of this Vlookup things as this is taking time @MohammedShabbir @ashokkarale

16

Yes, you can do it in linq with so much ease.

Read both Excel files into DataTables dt1 and dt2.
Add two new columns to the first table. (Use Add data column activity)
Use LINQ to find matching records in the second table based on SubAcc, copy Qty to Set1, and mark Set2 as Match or Mismatch.
Linq :
DT1.AsEnumerable().ToList().ForEach(Sub(r)
Dim m = DT2.AsEnumerable().
FirstOrDefault(Function(x) x(“SubAcc”).ToString = r(“SubAcc”).ToString)

r("Set1") = If(m IsNot Nothing, m("Qty").ToString, "")
r("Set2") = If(m IsNot Nothing AndAlso m("Qty").ToString = r("Qty").ToString, "Match", "Mismatch")

End Sub)

Finally, write results back to Excel.

1 Like
17

Hi @manoj2500

I have further simplified it. You can download code and have alook.
Mark it as solution, if it helps.

image
image412×174 3.08 KB

ExcelVlookup (2).zip (64.5 KB)
image
image296×358 12.7 KB

18

thanks i was already able to create the script.

1 Like
19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.