How to get specific data count from Excel sheet

Help
1

Hello All,

I have an excel sheet with different user names and contact details. I need to get count each user how many contacts they created. I have attached my sample data’s from excel.

Name Contact Name Created Time
User1 Contact1 2 Days ago
User1 Contact2 2 Days ago
User2 Contact3 2 Days ago
User2 Contact4 2 Days ago
User2 Contact5 2 Days ago
User3 Contact6 2 Days ago
User3 Contact7 2 Days ago
User3 Contact8 2 Days ago

Any help!

2

@yogavalli
Find starter help here:
yogavalli.xaml (8.5 KB)

For time reasons I did setup with generic data:
input:
grafik

Output:
grafik

3 Likes
3

Use read range: dt
then use assign
UserCount=dt.DefaultView.ToTable(true,“Name”).Rows.Count

1 Like
4

Hello @ImPratham45,

Thank you for your response. But I need the output like
user 1: 2,
user2 : 3
user3: 3.

Like that way count I need.

5

try this
CountProcess (2).zip (23.3 KB)

3 Likes
7

Thank you @ImPratham45.

Working great :grinning:

8

Welcome!

Happy Automation

9

Hello @ImPratham45,

I need to one changes here, I have one more column likes call count. I need to get sum of calls count.

Any Guide on this!

10

Use Compute method

dt.Compute("SUM(Calls), “”)

11

Hello @Annakarthi7,

I facing the below issue, when i am using your command.

image
image833×182 6.68 KB

12

Missed double quotes
dt.Compute(“SUM(Calls)”, “”)

13

Main.xaml (8.8 KB) test.xlsx (8.1 KB)

14

Hello @ImPratham45,

I am facing that Issue Assign: Conversion from string “” to type ‘Integer’ is not valid.

15

That means there is no data in that cell
then use if condition in for each row
If Row(“Acc”).Tostring=“”
then nothing else use that assign

16

Hello @ImPratham45,

I will explain clearly about my input and output, then it won’t make any confuses.

Input format :

CallerID Destination Duration
User1 12345678 0:51
User1 6785675798 0:49
User2 567578989 0:34
User2 12345678 0:08
User1 12345678 0:13
User2 12345678 0:17
User3 12345678 0:32
User3 896856453 0:17
User1 9688656454 0:23

Output Format:

CallerId Destination Count Total Duration
User1 4 2:16
User2 3 0:59
User3 2 0:49
17

Main.xaml (16.6 KB) Test.xlsx (9.1 KB)

18

Hello @ImPratham45,

Just now i cross checked, We have time format 12:51:00 format. So we are getting wrong output.

image
image820×367 17.8 KB

19

please change the data type in excel

20

Hello @ImPratham45,

I have changed datatype and I am trying to execute bot. facing the below issue.

image
image1191×242 13.4 KB

21

Oh sorry i didnt saw that value it is in time format
i will do it tom