How do I count the number of rows with same variable?

Screenshot 2023-07-27 175027

What activities should I use to count the number of rows with the same variable?
For instance, employee no 2 has a total of 9 rows and employee no 3 has a total of 4 rows.

@helpplease
Variables:

  • dataTable (DataTable) - Contains the data with “EmployeeNumber” and other details.
  • resultTable (DataTable) - To store the counts of rows for each employee.

Activities:
Excel Application Scope (or any other activity to read the data):

  • Read Range: “Sheet1” (or the name of your data source sheet)
  • Output: dataTable

Assign:

  • Left side: resultTable
  • Right side:
    (From row In dataTable.AsEnumerable()
    Group row By EmployeeNumber = row(“EmployeeNumber”) Into Group
    Select resultRow = New Object() {EmployeeNumber, Group.Count}).CopyToDataTable

Write Range (Optional - to output the result to an Excel sheet):

  • Range: “A1”
  • DataTable: resultTable

@helpplease
Read Datatable and use linq query to get number of rows against each group

groupedData = From row In dt.AsEnumerable()
                  Group row By Name = row.Field(Of String)("Employee Name (No)") Into Group
                  Select Employee Name, Count = Group.Count()

We can group the data and count the group members

Assign Acitvity
dictStatistic | Dictionary(Of String, int32) =

YourDataTableVar.AsEnumerable.GroupBy(Function (x) x("Employe Name (No)").toString.Trim.ToDictionary(Function (g) g.Key, Function (g) g.Count

OR

Build DataTable - 2 cols: Key, Count - dtStatistic

Assign Activity:

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("Employe Name (No)").toString.Trim into grp=Group
Let ra = new Object(){k, grp.Count}
Select r = dtStatistic.Rows.Add(ra)).CopyToDataTable

my data table is DT

@helpplease

CopyToDatatable() mention Parenthesis

Hi @helpplease

Read Range (Excel or CSV) → DataTable

Assign: employeeData = dataTable.AsEnumerable().GroupBy(Function(row) row("EmployeeNumber").ToString()).ToDictionary(Function(g) g.Key, Function(g) g.Count())

Log Message or Further Processing:
- For Each key-value pair in employeeData, you can log or process the employee number and its corresponding count as needed.

Hope it helps!!

Hi @helpplease

Use the below syntax:

Result= Dt.AsEnumerable().Count(Function(row) row("column Name").ToString() = pass the variable here)  (Datattype: system.Int32)

Hope it works!!

what is “= pass the variable here” ?

Its the variable you want to get count of

what does this mean?

@helpplease
It’s the variable name in which you store the numbers. If you want count of 2 pass 2 there.

Regards,

(From row In DT.AsEnumerable()
Group row By EmployeeNumber = row(“EmployeeNumber”) Into Group
Select DT.clone.Rows.Add({EmployeeNumber, Group.Count})).CopyToDataTable

Assign: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.

Hi @helpplease

Result= Dt.AsEnumerable().Count(Function(row) row("column Name").ToString() = "2")  (Datattype: system.Int32)

Hope it works!!

@helpplease

Remove double quotes and reassign

no, i want it to count without me inputting the employee number

The quotes are to be entered again

I am so sorry, I tried all the methods but it did not work. Is it possible if I can see the actual sequence, using the data I have for columns A and B? I don’t know what is wrong with the way I do…