How to count in each filter value in excel

Help
, ,
1

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

2

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

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

4

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

5

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

Col1 Count
aa 2
gg 3

6

@supermanPunch,

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

7

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

8

@supermanPunch ok i ill

9

Hi @supermanPunch

I want below output , please anyone help this

Emp

Regards,
Raja G

10

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

11

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

12

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

@supermanPunch thanks, i ill check

14

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

15

@Raja.G What is the Second Column ?

16

@supermanPunch same activity i need ā€œColumn 2ā€(second column) also

Dup

17

I expected output below,

Dup2

18

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

Hi @supermanPunch ,

Good afternoon

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

1 Like
20

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