Group by data table

Help
1

I need to group by data in a excel and specify number of times each data has occured.
Like input data is rose,lily,lotus,lotus,rose.
So output should be like rose-2 times, lily- 1 time, lotus- 1 time.
Hope you understand, the input and output data are in excel file format.
please help

2

There are probably several different ways to do this but here are two that come to mind:

In either case you will need to read the data from Excel into Data Table.

  1. If you know the full list of the different items you can keep a counter for each and while you loop through the rows with For each row, add one whenever you come across the specific item (use assign function like this counter_lily=counter_lily+1 and set the default value of the counter to zero)

  2. If you don’t know the full list of items, sort the table alphabetically and then loop through the table with for each row. For the first row get the name of the item and add 1 to your counter that had default value 0. For all other rows, take the name of the item and compare it with the previous one. If it is the same, add one to the counter. When you come to the point that they are different, save the name and count of the previous item into a new data table and start counting from one. Whenever you finish one item add a row to the new data table with the name and count. Make sure you save the name and count for the last item as well (you will need to have a separate logic for this – count the rows and save it when you know you are at the last row or do the saving after you exit the loop)

Maybe somebody will have easier solutions, but you can try this in the mean time.

3

Let’s assume your have datatable dt with column name “Name”.

Assign
Array(Of String) PeopleOccurences =
dt.AsEnumerable.GroupBy(Function(x) x(“Name”).ToString).
Select(Function(gr) string.Format(“{0} - {1} times”, g.Key, g.Count()).ToArray

4

not working, can you share ui path file,which you have created

5

Main.xaml (7.0 KB)

New Microsoft Excel Worksheet (2).xlsx (7.8 KB)

6

When you copied from forum somehow quote was spoilt. And I missed last bracket
See corrected version your workflow:
GroupByExample.zip (8.2 KB)

Also I added example of output results of group by to second sheet in excel.

2 Likes
7

thanks @Uemoe , got exactly what i wanted