How to get row value from excel based on row value of other excel sheet

Help Studio
, ,
1

Hi All,

I have excel sheet based on "Directory_number column value i have to get “Sales order” from other excel how to do that

Example

Sheet 1

DIRECTORY_NUMBER GROUP_ID TENANT SALES_ORDER
2086658807 a-339696 285427
2086658938 a-339696 285427
2086658990 a-339696 285427
2525142003 a-319652 300826
2526330717 a-319652 300826
Sheet 2
SALES_ORDER_ID DIRECTORY_NUMBER
182908774 2086658807
Output

DIRECTORY_NUMBER|GROUP_ID|TENANT|SALES_ORDER
2086658807|a-339696|285427|182908774Datasheet.xlsx (15.9 KB)

Can some one help me on this

Regards,
Hima

2

Hello @thima

  1. First you can read both sheets and save ip1 and ip2.
    image

2.Then use for each for and pass ip1 datatable.
image

  1. Assign DirectoryNumber=CurrentRow(0).ToString

  2. Use lookup datatable to find sales order id from ip2. In properties,
    Datatable=ip2
    lookup value=DirectoryNumber
    lookup column Name= “Directory_Name”
    output cell value = SalesID
    Target Column name= “Sales_Order_ID”

  3. Assign CurrentRow(3)=SalesID

6.Then use write range outside for each and write ip1.

image