How to count in each filter value in excel

Hi Guys,

I need to count in each filter value in excel using uipath

For example,
i attached excel and result

test.xlsx (8.5 KB)

Result:
aa count is 2
gg count is 3

Please anyone help this

Regards,
Raja G

@Raja.G

Follow below steps.

  1. Use Read Range activity to read the data from excel file and will give you output as DataTable and say ‘inputDT’.

  2. And then try below expression.

           uniqueDT = inputDT.DefaultView.ToTable(True,"ColumnName")
    
       ForEach row in uniqueDT
           Int filterCount = inputDT.Select("[Column Name] = '"+row("ColumnName")+"'").CopyToDataTable.Count
2 Likes

@Raja.G If you haven’t found the Solution yet , Can you tell us Where do you want the Count to Be Stored?

@supermanPunch I need to store the result in new sheet in same excel

@Raja.G So you want Something Like this Format ?:

Col1 Count
aa 2
gg 3

@supermanPunch,

I want column 1 filter each value count and Column 2 filter each value count

@Raja.G Can you show us a Sample Output for the Test File given , with that we can confirm what needs to be done :sweat_smile:

@supermanPunch ok i ill

Hi @supermanPunch

I want below output , please anyone help this

Emp

Regards,
Raja G

Hi @supermanPunch,

Repeat count just a output column ,that column i need to show next sheet in same excel, can u help this

Regards,
Raja G

@Raja.G I can Understand Ian Count is 2, Sandeep Count is 2 but for Ross Repeat_Count Should be Ross count is 3 Right, or is that a Mistake ? Also I didn’t understand How did you put 34 count is 3 for the Name John, I did not get the Pattern you used :sweat_smile:

@Raja.G Check this workflow, It is Similiar to what you want, but it is not Completely Satisfying the output Format you provided :sweat_smile:
RepeatCount.zip (12.7 KB)

1 Like

@supermanPunch thanks, i ill check

Hey @supermanPunch,

Working fine but one column only , i need each column, if u free please can u help .

In this below code one column only worked , i tried to add my second column but i couldn’t able to that.

(From p In DT.AsEnumerable() Group By x= New With { Key.a =p.Item(“column 1”)} y = New With {key.a = p.item(“column 2”)} Into Grp = Group Select resultDT.Clone.LoadDataRow (New Object() {grp(0)(0),grp(0)(0).toString+" Count is "+grp.Count.toString},False)).CopyToDataTable

Regards,
Raja G

@Raja.G What is the Second Column ?

@supermanPunch same activity i need “Column 2”(second column) also

Dup

I expected output below,

Dup2

@Raja.G This is the Closest Format I could come up with There might be other ways, but for now i could only Come up with this idea, If those two Columns needs to be present on Separate Sheets then there is a way but together I couldn’t find the Fastest method. :sweat_smile:

Check this :
RepeatCount.zip (12.5 KB)

1 Like

Hi @supermanPunch ,

Good afternoon

Thanks for your code i just add small modification and i got a output

1 Like

@Raja.G After Modification do you get the Output as how you needed?