Excel matching two files and write cell for match values

Help Studio
, ,
#1

Dear All,

I have two excel files

  1. mapping customer code excel
    image
  2. master customer code excel
    image

What i want to do is read 1st mapping excel (column C and column E) and then write cell in 2nd excel (column B) where site codes are matching with looping.

Let me know any suggestion for this case. Many thanks for your help.

#2

can you share expected output

#3

@Htwe_Ko_Ko_Wynn, Here is a high approach.

Step 1: Use Read Range activity and capture the output in DataTable variable (e.g. dt_Customer)
Step 2: Loop through excel rows. (Use For Each loop within Excel Application Scope)
Step 3: Use Read Cell activity to get site code value in string variable. (e.g. siteCode)
Step 4: Use Write Cell activity and assign value as: dt_Customer.Select(“CityMard Customer Code=’” + siteCode + "’)(0)(“SAP Customer Code”).ToString

Hope it helps.

#4

This is my expected output bro

image

#5

You can use VlookUp formula in write cell and then using autofill u will get all data for SAP Customer

1 Like
#6

Let me try your solution. thanks bro

#7

Hi Bhavik,
I tired your solution, however, i got some misunderstanding and i would like you to check my xaml if you have time

test2.xaml (10.4 KB)
Sitecode test.xlsx (9.0 KB)
sitecode mapping.xlsx (7.9 KB)

#8

Check this: test2.xaml (9.6 KB)

#9

hi @Htwe_Ko_Ko_Wynn,

You can simply use LookUp Datatable activity inside for each row activity.
using lookup datatable activity you can search for a value in datatable and returns value from another column.

Check out this example…

1 Like
#10

Many thanks for your second time help Bhavik.

#11

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