Need help with adding excel cells from different sheets

So I have a school project that is due next week so I am in desperate need of help, I hope to get this assignment done ASAP but I am very very bad at tech so be patient with me.

Basically what I need to do is to add the sum of an item from 3 separate excel spreadsheets and fill up a google form, however, I have no clue how. I will be attaching what I mean.

So from the images, you can see that there are 5 items that are the same on each sheet, what I need help to do is to add the quantity of each item individually, for example, 2 milk(From sheet 1) +8 milk(From sheet 2) + 0 milk(From sheet 3) together to get 10 milk. What activities and functions must I use in order to allow this to work?



Hi,

you can use “Read range” to read the three worksheets and store data into data-table. Then use “Merge Data Table” to put these three data-tables into an one. Finally, with LINQ you can found the sum of these items:
dict = dt.AsEnumerable.GroupBy(Function(row) row(“Item”)).ToDictionary(Function(z) z.Key.ToString, Function(y) y.Sum(Function(row) row.Field(Of Integer)(“Number”)))

The you will obtain a dictionary with the name of the items as Key and the the sums of the cells as values.

Regards, Gio

1 Like

@Tech_noobie - Another approach would be…

Datatables - I have created 3 datatables dt1 dt2 and dt3 with the same data…

Merge Datatables
First Merged Dt1 - dt2
Then dt2 to dt3…

Asisgn DtOut = dt3.Clone

DtOut =

  (From d In dt3.AsEnumerable
  Group d By k=d("Item").toString.Trim Into Group
  Select DtOut.Rows.Add({k, Group.Sum(Function (x) CInt(x(1)))})).CopyToDataTable

MyOutput

image

If You Number column is string, then tweak the above code to

 (From d In dt3.AsEnumerable
 Group d By k=d("Item").toString.Trim Into Group
 Select DtOut.Rows.Add({k, Group.Sum(Function (x) 
  CInt(x(1).ToString))})).CopyToDataTable

And finally if you Number column values are not integer or bigger numbers, just change from Cint to Cdbl…

Hope this helps…

1 Like

Hi sir, after trying out what you have provided, I am facing issues with the Dt.Out as it has error, I hope you can assist me on what to do to troubleshoot

@Tech_noobie - sure. please show us your workflow and the error?

@Tech_noobie - Dtout is of variable type datatable. Also you can rename it dtout from dt.out.

I see you haven’t declared any of the datatable.

Let me share my workflow sample.

1 Like

@Tech_noobie - Please find the starter help here…
Merge_Sum.zip (44.5 KB)

1 Like