Group by in Excel and sum values to create batch

Hi All,

I have data in below format -

My req -
1.
I need to group the data by Site and my data should look like -

  1. I need to create batch data for each site separately ( meaning I need to enter 4 records for first site) and sum the Amount of each sites ( FIS = 40 ) and store in different excel.

Any help or xaml will be appreciated.

Hello @Zahid1
you can easily do all the tasks using Linq

You can do this task using OrderBy method.It’ll sort all the data as shown in the image and it’ll write the data in a new sheet.

For this task first i grouped all the data and took the distinct value’s than looping those data filtered it one by one and wrote in a different and naming the sheet of the site.
So what we get is a batch of different and then used Sum method to sum the Amount Column and wrote it in our batch Sheet.

Check this workflow and excel for better understanding

Filter_Site.zip (28.0 KB)

Hi Vicky it’s working perfectly, thanks a lot for quick response.

Regards,
Zahid

Hi Vicky,
I am facing a small issue, the Account ID has zero’s prefixed which is getting truncated.
File attached. forumTest.xlsx (9.2 KB)

Could you please look into this.

Thanks in advance.

1 Like

Hello @Zahid1
Saw your sheet and the issues with it.
I have added a Non breaking space to all the values in the accounts column and wrote it once again in a sheet so now the excel sheet wont automatically truncate all the zero’s

Check this workflow for better understanding
SortingBuildDatatable.xaml (15.9 KB)
orignal forumTest.xlsx (14.9 KB)

1 Like

Hi @vickydas,

I tested the xaml and it’s working perfectly.
Thanks a lot bro !!!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.