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

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.

image

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

@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:

1 Like

Hi @foxngn You can also try Filter Datatable

And get the count from there

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?

image

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

Thanks

Build datatable should look like this:
grafik

see below

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

Just try as described and let us know the result

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

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