Lookup Data table ->orderNo ,TransNo->ouput ValueDate

[Anand_Designer (Anand_RPA)]

Hi,
i have 2 excel File.

orders.xlsx

2nd is statement.xlsx

i have to vlookup from order.xlsx column Name is Order No to Trans No In Statement .xlsx . if both mathed i have to take value of Value Date and paste into Order.xlsx .

OrderID	Time	Transaction Date	Order No.	ValuDate
AH4512	15:35:26	11-Jan-22	PYTM220600834114	3/2/2022

Statement.xlsx (8.7 KB)

Orders.xlsx (9.4 KB)

[Lak_Ui (Lakshmi)]

1.read range for both the tables .convert to datatable dtorder and dtstatement
2.loop through Trans No in dtstatement
3.Inside loop, add lookup range activity and search the transno in dtorder and get the cell no
4.use wreite cell activity to write date to the corresponding cell

[Anand_Designer (Anand_RPA)]

i have tried but i am getting error like

[Anand_Designer (Anand_RPA)]

Can you look this issue ?

Vloopup_ValueDate.xaml (10.3 KB)

[Lak_Ui (Lakshmi)]

order no will be repeating in order excel while it will be distinct in statement excel right?

[Lak_Ui (Lakshmi)]

Vloopup_ValueDate.xaml (15.2 KB)
I have update the wf with a different logic now. Please check if it helps

[Anand_Designer (Anand_RPA)]

hi,

i have changed order num in Order.xlsx. tested it.

its writing only first row of Statement.xlsx. its repeating same row in Value Date →
its not writing matching Data…

Orders.xlsx (11.1 KB)

Statement.xlsx (8.7 KB)

[Lak_Ui (Lakshmi)]

Vloopup_ValueDate.xaml (17.7 KB)
I forgot to add filter condition check this

[Lak_Ui (Lakshmi)]

Hi @Anand_Designer ,

Did it work?

[Gangadhar_Athili]

Try This Query :

(From r1 in DT1.Asenumerable
Let a = DT2.Asenumerable.where(Function(r) r(“Trans No”).Tostring.Equals(r1(“Order No.”).Tostring))
Let b = if(a.count>0,a(0)(“Value Date”),"")
Select CloneDT1.Rows.Add(r1.itemArray.append(b).Toarray)).Copytodatatable

[Anand_Designer (Anand_RPA)]

its working fine. Tq