How do i take 2 or more data tables of the exact same size, and combine the values so they are added together into 1 data table?

Help Forum
1

I have the below data tables. How do i combine them into 1 table where the values are added together. Merge doesnt add the values together so i am a bit stumped.

Effort (hrs)
0.00
0.00
40.00
2.00
0.00

Effort (hrs)
3.00
5.00
80.00
1.00
1.00

Output should look like this:
Effort (hrs)
3.00
5.00
120.00
3.00
1.00

2

Hi @davidgoderre :smile:
Here is a suggestion to solve your case: Main.xaml (10.8 KB)
Let us know if it is unclear or does not solve your issue !

3

I downloaded the Main.xaml, but i get a Missing or invalid activity. See screenshot below. what am i missing there?

image

4

I resolved the dependencies, I am reviewing your solution now.

5

Ok so it works mostly. However I am sure 2 decimal digits. And my data did not add properly using your solution.

I used a scaled down version when I posted my question, and so that is probably my fault. My data looks like the below.

DT1
40.00
10.00
80.00
0.00
0.00
0.00
270.00

DT2
40.00
10.00
80.00
0.00
25.00
0.00
25.00

And my output came out like the below

42.00
12.00
82.00
2.00
2.00
2.00
272.00

Is this because the values are not formatted correctly?

6

Hi @davidgoderre

How are you getting the values in DT1 and DT2? Are you reading these tables from the excel files using the read range?

The output of yours is showing that it is adding 2 in every value of DT1

7

You are correct, I am using the read range activity from an excel file. Is that ok?

8

@davidgoderre

Can you share the excel files with the dummy keeping the formatting same?

9

Sure. I would like to point out that, I am only pulling a specific range from the sheet, (Ex. A1-A30) from the excel file. So just 1 column of numbers. I attached 2 files with only the dummy data I pull.

DummyData2.xlsx (8.6 KB)
DummyData1.xlsx (8.6 KB)

10

@ppr is this achievable with linq?

11

In general
Possible with linq
Or With foreach and using the index output

12

Hi @davidgoderre

I am getting the correct output with the xaml provided by @Hiba_B.

Can you show us your implementation?

13

i read data table from the excel sheets. Then i try to combine using the xaml from Hiba_B and it gets the output i put earlier. I am not sure what is causing it to add 2 instead of adding the numbers together.

I figured out a way to do it by reading the cells and putting them into another sheet with a calculated column. for now this will suffice.

14

Hi @davidgoderre

Can you give it a try

(
	From row In dt_1
	Let r =CDbl(row(0))+CDbl(dt_2.Rows(dt_1.Rows.IndexOf(row))(0))
	Select dt_Result.Rows.Add(r)
).CopyToDataTable

CombineDataTableValues.xaml (6.8 KB)

15

This worked perfectly! I think I can use this now to run multiple times with more than 2 spreadsheets! thank you

16

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