Group by and sum of 2 columns in an excel and compare with other excel sheet

Help Activities
, ,
1

In this current project, I need to do group by invoice number and sum of debit amount column and then i have to compare it with other excel sheet’s debit amount and update the status if found.

input:

Excel1
invoice number Debit status
12345 500rs
12345 100rs
12345 400rs

Excel 2
invoice number Debit
12345 1000rs

Output
Excel1
invoice number Debit status
12345 500rs Found
12345 100rs
12345 400rs

I need to do the sum of all debit amounts for invoice number 12345 in excel1, then search for same invoice number 12345 in excel2 and compare respective debit amount. if match found, then update status on excel1.

please help me with

  1. how to do group by and sum and then compare with second excel file.

thanks:)

2

Hi @praveena.nadi

  1. Read both Excel files using the Read Range activity to store the data in two separate DataTable variables, let’s call them dtExcel1 and dtExcel2.
  2. Use the Group By function to group the data in dtExcel1 by the “invoice number” column and calculate the sum of the “Debit” column. This will create a new DataTable with the grouped data.

Dim dtGrouped As DataTable = dtExcel1.AsEnumerable() _
.GroupBy(Function(row) row.Field(Of String)(“invoice number”)) _
.Select(Function(group) New With {
.InvoiceNumber = group.Key,
.TotalDebit = group.Sum(Function(row) Convert.ToDecimal(row.Field(Of String)(“Debit”)))
}) _
.CopyToDataTable()

  1. Iterate through the rows of dtGrouped using a For Each Row activity.
  2. Inside the loop, use the Select method to find the corresponding row in dtExcel2 where the “invoice number” matches the current row in dtGrouped. If a match is found, update the “status” column in dtExcel1 accordingly.

Dim invoiceNumber As String = row.Field(Of String)(“InvoiceNumber”)
Dim debitAmount As Decimal = Convert.ToDecimal(row.Field(Of String)(“TotalDebit”))

Dim matchingRow As DataRow = dtExcel2.Select(“invoice number = '” & invoiceNumber & “'”).FirstOrDefault()
If matchingRow IsNot Nothing AndAlso Convert.ToDecimal(matchingRow(“Debit”)) = debitAmount Then
row.SetField(“status”, “Found”)
End If

Thanks!!

3

@praveena.nadi

If you are doing inside loop…and say you are looping on first table dt1…and have data in second dt2

sumvalue = Dt2.AsEnumerable.Where(function(x) x("InvCol").ToString.Equals(currentrow("InvCol").ToString)).Sum(function(x) CDBL(x("Amount").ToString))

Sumval is of double type

If you want to group by and get sum of all values then check this…its a similar case I have given here

Cheers

4

Thank you ill try and let you know :blush:

1 Like
5

@Anil_G ,

( From row In DtExcel.AsEnumerable
Group row By k=row(“Invoice Number”).ToString.Trim
Into grp=Group
Let cs = grp.Sum(Function(x) CDbl(x(“Debit”).ToString.Trim))
Select DtResult.Rows.Add({k, cs.ToString})
).CopyToDataTable

This is working for test data where i have only 2columns(VNO, Debit) but when i consider original file which has few more columns in between, its not working.
please help me with this.

thanks:)

6

@praveena.nadi

DtResult should be created with two columns…then it would work…are you doing that?

cheers

7

@Anil_G

DtResult = DtOrig.Clone

This is what i am doing

8

@Anil_G

This is how my original file looks like where VNO= Invoice Number, Debit=Sum amount to be grouped by

image
image919×304 7.99 KB

9

I just need to do get sum by doing group by of invoice number(VNO) if present multiple invoice numbers,if not distinct ones should take the respective debit values only.

10

@praveena.nadi

Use a build datatable and build the dtresult with two columns both as string type…

If you use dtorig.clone…it needs all columns…as we dont need all in output only two are included and statement given will give datatable with two columns output …first invoice and second sum …converted to string

Cheers

11

@Anil_G

In this case, how to load data from original File into this new datatable?

as original excel file which has all columns are stored in dtOrig, and new datatable would need only 2 columns
the how to load?

12

@praveena.nadi

Notmally read the data into dtorig…and then build a datatable for dtresult with two columns and the above expression will load the grouped data into the dtresult…

So from excel you will get 10 columns may be…after grouping as you need only invoice nd sum the dtresult will contain only those two…its a transformed table

Cheers

13

@Anil_G

I understood, but I need one more column from excel file to be there in output datatable which has grouped data.

how to get this?

14

Because, From the excel file image below,

image
image919×304 7.99 KB

I first need to filter by using VNO Column(HO-CN), and then i need to do group by using Ins.tax invoice (K Column) and respective Sum of debit column.

so if i use the grouped output datatable which has only invoice number and sum of debit amount columns, I am not able to filter VNO columns.

Please guide me.

15

@praveena.nadi

Can you tell which column you need and do you want to group on that also?

Or need any specific row value or so?

Can you pleasee plain that accordingly we can modify the query

This is where we can add

Cheers

16

image

@Anil_G
Above are the 3 columns i need from original excel file.
out of which, first i need to filter by VNO column and then group by Ins.Tax Invoice and do the sum of debit amount and then compare with other excel sheet.

this is the overall view.

17

@praveena.nadi

Then you can use the above…here in the where condition you can include vno also and then do sum

sumvalue = Dt2.AsEnumerable.Where(function(x) x("InvCol").ToString.Equals(currentrow("InvCol").ToString) andAlso x("VNo").ToString.Equals("Whatvalueyouwanttofilter")).Sum(function(x) CDBL(x("Amount").ToString))

If you want to do by group by statement all at once…then I want to understand vno as well…like if you filter on vno ideally you would get only one or teo rows…so is it like you want to group by vno and invoice? And then get sum of combination or group by?

If so use like this

( From row In DtExcel.AsEnumerable Group row By k=row(“Invoice Number”).ToString.Trim,k1=row("vno").ToString.Trim Into grp=Group Let cs = grp.Sum(Function(x) CDbl(x(“Debit”).ToString.Trim)) Select DtResult.Rows.Add({k, k1,cs.ToString}) ).CopyToDataTable

Now in build have 3 columns and now group by considers combination of both columns

Cheers

18

sumvalue = Dt2.AsEnumerable.Where(function(x) x(“InvCol”).ToString.Equals(currentrow(“InvCol”).ToString) andAlso x(“VNo”).ToString.Equals(“Whatvalueyouwanttofilter”)).Sum(function(x) CDBL(x(“Amount”).ToString))

I did not get this exactly.Please can you explain in brief.

Since i want to do group by with having all columns in the datatable,because at the end, i have to write this datatable back to csv file with updated status .

19

@Anil_G

I have a debit column in excel1 whose values are printing as 2940,1750,2000 like so…
another excel sheet whose debit column whose values are printing as -2,940.00,-1,750.00,-2,000.00 like so

both are string datatypes.

when im tryng to compare these two columns, I should get “YES” as the value is same even if it contains “-”, or “,” in between.

how to do this.

20

@praveena.nadi

While comparing use Match.Abs(cdbl(stringvar))

Csbl will take care of comma and abs will take care of minus

Cheers