Linq query to compare two datatatables and sum column values of matching row

Help Studio
, ,
1

Hi Team,

I have 2 excels

excel1

Col1,Col2
A 4
B 5
C 6

Excel2
Col1,Col2
M 6
N 7
B 8

if Col1 of excel1 matches Col1 of excel2 add Col2

eg B matches in both then my answer shoulld be 8+5

new datable will be
col1,col2
B 13

regards,
supriya

2

Hi @supu123

Kindly refer the below solution,

Hope this will be helpful. Thank you.

3

@supu123

  1. Use Read Range activity to read the data from excel files and it will give output as DataTable. Let’s say InputDT1 and InputDT2.

  2. DataTable outputDT = InputDT1.Clone

  3. And then try below Linq query.

OutputDT = ( From a in InputDT1 Join b in InputDT2 On a("Col1").ToString Equals b("Col1").ToString Select OutputDT.Rows.Add({a("Col1"), Cint(a("Col1").ToString.Trim)+Cint(b("Col1").ToString.Trim)}))

1 Like
4

Hi

Error while using this formula

WhatsApp Image 2021-12-09 at 3.56.52 PM
WhatsApp Image 2021-12-09 at 3.56.52 PM1599Ă—899 65.6 KB

Thanks and Regards,
Supriya

5

@supu123

At the end of the expression write CopyToDataTable.

OutputDT = ( From a in InputDT1 Join b in InputDT2 On a("Col1").ToString Equals b("Col1").ToString Select OutputDT.Rows.Add({a("Col1"), Cint(a("Col1").ToString.Trim)+Cint(b("Col1").ToString.Trim)})).CopyToDataTable

1 Like
6

There is a small mistake in terms of Columns in the expression…

Updated:
OutputDT = ( From a in InputDT1 Join b in InputDT2 On a(“Col1”).ToString Equals b(“Col1”).ToString Select OutputDT.Rows.Add({a(“Col1”), Cint(a(“Col2”).ToString.Trim)+Cint(b(“Col2”).ToString.Trim)})).CopyToDataTable

Regards.

7

Hi @lakshman

Formula is working for me but facing a issue

formula
formula1600Ă—1200 94.9 KB

modified the formula to get the number in the format ##,###.##

but in the excel it is throwing an error after write range

modified
modified1599Ă—899 102 KB

how to handle this

Thanks and Reagrds,
Supriya Yenaganti

8

more over excel is of format “.xls”