Match column and update after concatinating to another column

Help Activities
, ,
1

Match “Made #” in both worksheets.(Sheet1 and Sheet2)
If “Made #” is matched
Concatenate Made #, Made Field 2, Made Field 4 in Sheet 1
Format: Made# - Made Field 2 Made Field 4
Replace “Made #” value in Sheet2 worksheet.
Lookup the Made# from Sales worksheet to return “Made# - Made Field 2 Made Field 4” value.
(If made # in both sheets match, then we need to concatenate Made #, Made Field 2, Made Field 4 from Sheet1 and update in Sheet2 for Made #)
Sample.xlsx (10.3 KB)

2

Hello @Shilpa_Mohanty ,

Use the below code to get the matching records from both the sheets.

inputDT1.AsEnumerable().Where(Function(row) inputDT2.AsEnumerable().Select(function(r) r("ColumnName").ToString).Any(Function(x) x = row("ColumnName").ToString)).CopyToDataTable

Once you will get the records have a look on this video how you can combine the columns.

Thanks,
Sanjit

3

thanks for the solution but we r not allowed to use invoke code

4

use build datatable activity and create 4 columns of type string
Transaction Date, Transaction #, Made #, Name

then use the below code to combine the columns in assign activity.
Left = Newdt
Right = (From row in DT.AsEnumerable() Select Newdt.LoadDataRow(New Object(){
row (“Transaction Date”).ToString,
row(“Transaction #”).toString,
row(“Made #”).toString,
row(“Made Field 2”).toString+" “+row(“Made Field 3”).toString+” "+row(“Made Field 4”).toString
)},false)).CopyToDataTable

5

both my sheets contains different columns
Sheet 1 has 27 columns
sheet 2 has 32 columns
is there any other feasible way rather than declaring all column names?

NOTE : only for reference i attached sample excel

6

In the output you want the columns from sheet1 or sheet2 ?

7

@Shilpa_Mohanty
Kindly refer to this, Already posted in your previous post, have a look at it

8

Duplicate of Match column and update after concatinating to another column