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

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)

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

i have tried but i am getting error like

Can you look this issue ?

Vloopup_ValueDate.xaml (10.3 KB)

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

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

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)

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

Hi @Anand_Designer ,

Did it work?

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

its working fine. Tq

1 Like

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