From the looks of it, you will need to use a loop and Assign activites, rather than the Merge activity.
You can probably use a .GroupBy() line of code too but I can’t say I know it very well.
so to do this with a loop, you basically just need to “Add column” and Assign to rename columns.Then, run through dt1 in a For Each with another For Each for dt2 inside where you can pull those items that match.
Assign dt1.Columns(0).ColumnName = "ID"
Assign dt1.Columns(1).ColumnName = "Value1"
Add Data Column // to dt1 with "Value2" for columnname
Add Data Column // to dt1 with "Diff" for columnname
Add Data Column // to dt2 in order to cross off when a match is made
For each row1 in dt1
For each row2 in dt2
If row1(0).ToString.ToUpper.Trim = row2(0).ToString.ToUpper.Trim AND row2(2)=""
Assign row1("Value2") = row2(1).ToString.Trim
Assign row1("Diff") = CInt(row1("Value1").ToString.Trim) - CInt(row1("Value2").ToString.Trim)
Assign row2(2) = "X" // used to cross off row when it has been used
So that’s pretty much it.
—You set up data table with the 4 columns
—Add 1 to keep a status on second table to cross off when a row is used
—Then, Loop through each row of first table and match with second table
—If match is found and has not been added yet, then Assign value to first table and take difference
There are ways to use vb.net to make this more efficient as well, and there are better solutions I’m sure. But, I hope this helps with my limited time to provide an answer.