Get Unique Values only and their sum

Help Studio
, , , , , , , ,
1

Hi Guyzz,
I was working on one solution ,where i have to add the Price based on the Names and Date , if the date is coming on this month and the name is same then we have to sum up the values and put in different sheet.
eg:
image

Output:
image

Can anyone please help me on this!!!

2

Hello @Anjali_Rani

Read Data:

  • Use “Read Range” to read data from the input Excel sheet.

filteredRows = yourDataTable.AsEnumerable().Where(Function(row) DateTime.Parse(row(“Date”).ToString()).Month = DateTime.Now.Month).CopyToDataTable()

groupedRows = filteredRows.AsEnumerable().GroupBy(Function(row) row(“Name”).ToString()).Select(Function(group) New With {
.Name = group.Key,
.TotalPrice = group.Sum(Function(row) Convert.ToDouble(row(“Price”)))
}).ToList()

Write Range (groupedRows) to Output Excel

Thanks & Cheers!!!

3

@Anjali_Rani

use this linq query

dt.AsEnumerable().Where(Function(row) DateTime.ParseExact(row("Date").ToString(), "yyyy-MM-dd", CultureInfo.InvariantCulture).Month = DateTime.Now.Month AndAlso row("Name").ToString() = "YourName").GroupBy(Function(row) row("Name")).Select(Function(group) dt.Clone.LoadDataRow({group.Key, group.Sum(Function(row) Convert.ToDecimal(row("Price")))}, False)).CopyToDataTable().WriteRange("OutputSheet", "A1")

cheers…!

1 Like
4

I am getting this error, though i have values in dttest DT

image
image1536×698 49.9 KB

image
image1261×570 32.7 KB

image
image1228×579 47.3 KB

5

I am getting this error on this

image
image1324×452 25.6 KB

Can you please check.

6

@Anjali_Rani

you are using slanted double quotes (“” ) instead of straight double quotes ("" ). The correct syntax should use straight double quotes.

filteredRows.AsEnumerable().GroupBy(Function(row) row(“Name”).ToString()).Select(Function(group) New With {
.Name = group.Key,
.TotalPrice = group.Sum(Function(row) Convert.ToDouble(row(“Price”)))
}).ToList()

7

What should be the Datatype of variable on which i have to write this query?

8

@Anjali_Rani

Data type : List of Data Rows

9

@Anjali_Rani

you can store in list(datarows)