Get Unique Values only and their sum

[Anjali_Rani]

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:

Output:

Can anyone please help me on this!!!

[Kartheek_Battu]

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!!!

[sai_gupta]

@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…!

[Anjali_Rani]

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

[Anjali_Rani]

@Kartheek_Battu

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()

I am getting this error on this

Can you please check.

[Kartheek_Battu]

@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()

[Anjali_Rani]

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

[Kartheek_Battu]

@Anjali_Rani

Data type : List of Data Rows

[sai_gupta]

@Anjali_Rani

you can store in list(datarows)