How to count unique row value & insert into the same excel sheet?

[foxngn (Dylan Ngiam)]

Hi All,

Basically I’m very new to UiPath & I doesn’t possess with any VB knowledge.
Right now I’m stuck here as I’m trying to get a unique row count for a single column.

As below, I got a range of data, but i would only want to get the unique count of each of the category.

and make it to be able to write down on a same sheet to something like this:

I tried to use the pivot table activities but i unable to get the same result as i done pivoting inside Excel.

I tried to use the IF activities, but i unable to simulate it to write on the same sheet, i only manage to do it to present as a messagebox.

May i know what is the best & simplify way for me to get the result that i seek for?

Thanks

[ppr (Peter)]

@foxngn
create a report datatable with build datatable activity - dtReport
Columns: Category, Count

use an assign activity
left side: dtReport
Right side:

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("Category").toString.Trim into grp=Group
let ra = new Object(){k, grp.Count}
Select r=dtReport.Rows.Add(ra)).CopyToDataTable

Also have alook here:

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[Sudharsan_Ka]

Hi @foxngn You can also try Filter Datatable

And get the count from there

[foxngn (Dylan Ngiam)]

Thanks Peter,

In my case, is there a need to build 2 datatable as shown in the guide file for it to copy?
Also when you mentioned the columns: Category does you meant something like this?

Also if my raw report got alot of other irrelevant column, will this hinder the current activity?

Thanks

[ppr (Peter)]

@foxngn

Also when you mentioned the columns: Category does you meant something like this?

Build datatable should look like this:

@foxngn

is there a need to build 2 datatable

see below

@foxngn

Also if my raw report got alot of other irrelevant column, will this hinder the current activity?

no, thats the reason why we create this 2 column report structure.

Just try as described and let us know the result

[foxngn (Dylan Ngiam)]

I managed to get my result by doing the below activities:

May i know if i want to loop in another column, do i need to create another excel application scope?

Thanks

[ppr (Peter)]

You will use additional EAS in case of you are working on another EXCEL file