Select method to filter excel sheet

[Dmitri00007 (Dmitri)]

I’m using this method to filter my excel sheet
(
From row In out_excel1_DataTable
Group row By a = row(“Column B”).ToString Into grp = Group
Select grp.First
).CopyToDataTable

“column B” contains bunch of unique invoice number, suppose (invoice number = 1234, has 50 rows) and every single row has a price column, we just need single row data of single invoice but we need sum of price from the 50 rows.

please help me I’m stuck here

[Dmitri00007 (Dmitri)]

take this sample sheet, invoice number is repetitive and we need just one row of one invoice. with sum of incident cost.

one entry on invoice no. (12345) and sum of incident cost L2:L85
01.xlsx (100.9 KB)

[Jobin_Joy]

Hi @Dmitri00007

Can you try this?

var result = (from row in YourTable
group row by new {row.InvoiceNumber}
into grp
select new
{
grp.Key.InvoiceNumber,
Quantity = grp.Sum(row => row.IncidentCost)
}).ToList();

Thank you.

[Jobin_Joy]

Alternately you can try the below one as well,

dtResults = (From dte In dtTable.AsEnumerable
Group dte By col1=dte(2).ToString.Trim Into Group
Select dtResults.Rows.Add({col1, Group.Sum(Function (x) CInt(x(1).toString.Trim))})).CopyToDataTable

Note: Give the correct index of the column in the highlighted (in Bold) places.

Hope this will help you. Thank you.

[Dmitri00007 (Dmitri)]

The Datatabe variable name is out_excel1_DataTable
Column name is Invoice Number
sum Column name - Spot Net Amount

I’m getting this error

[Jobin_Joy]

@Jobin_Joy

dtResults = (From dte In dtTable.AsEnumerable
Group dte By col1= dte(2) .ToString.Trim Into Group
Select dtResults.Rows.Add({col1, Group.Sum(Function (x) CInt( x(1).toString.Trim))})).CopyToDataTable

Note: Give the correct index of the column in the highlighted (in Bold) places.

@Dmitri00007
Please try with this.

I can see the syntax error due to incorrect spelling in your above screenshot.

Thank you.

[Jobin_Joy]

Hi @Dmitri00007

If you are very new to this, let me know I can share xaml file here.

Thank you.

[Jobin_Joy]

Hi @Dmitri00007

Here is the code for your requirement,

I have added a new sheet (Result) in the same excel file store the result. If need, you can store it in a separate excel file as well.

Result:

Code:
GroupBy.xaml (7.9 KB)

Kindly mark this post as solution, if this one is helped you.

Thank you.

[Dmitri00007 (Dmitri)]

Thanks for your help. I’m able to filter it.
Yes, this is my first month of learning.

[Jobin_Joy]

Hi @Raj_Ayush_Gupta

So glad to hear that this is your first learning and the code is working fine now.

Please mark my above post as ‘Solution’ so that it will be helpful for others as well.

Thank you.

[Dmitri00007 (Dmitri)]

Marked, thank you so much.

I need one more help, just a question.
Can we use whildcard in string value {Assign Activity}

“C:\Users\Downloads\excel/” + today.ToString(“MMMMyyyy”)+“/SpotsReport_”+Today.Tostring(“dd_MM_yyyy_”)+“4567865.zip”

4567865 replace this value with whildcard.

Using Assign Activity

[Jobin_Joy]

Hi @Dmitri00007

Please create new Topic for this question. So that it will be easy to isolate topics.

Just copy paste and create a new one.

Thank you.