Count the number of occurrences of each value in an excel column

Help Studio
, , , ,
1

Hello all,
I would like to know how to count the number of occurences of each value in excel column along with each value’s values name:
input example:

image

I would like the output to be :
Column 1 Column 2
abc 4
efg 2
xyz 2

Thanking you,

2

Hi @Yugal_Raju

Please try this query:

(From row In dt.AsEnumerable()
Group row By colValue = row("Column1").ToString() Into Group
Select colName = colValue, colCount = Group.Count()).ToDictionary(Function(x) x.colName, Function(x) x.colCount)

Edit: The above query will give you the output in the form of a Dictionary, where the Col1 data & their count will be stored in key-value pairs.

If you want the output in the form of data table, please refer the following query:

(From row In dt.AsEnumerable()
Group row By colValue = row("Col1").ToString() Into grp = Group
Select dtUpdated.LoadDataRow(New Object(){
	colValue,
	grp.Count()
	},False)
	).CopyToDataTable

Hope this helps,
Best Regards.

3

Can you please explain how to assign this query ?

4

@Yugal_Raju

Please take a look at the following workflow for your understanding:

image
image730×459 14.6 KB

Here, you have a pre-built datatable ‘dtUpdated’ with 2 columns (Value & Count). Then you are executing a query to group similar data. Once the grouping is done, you load a datarow to the dtUpdated with the group value & group count.

Input:

image

Output:

image

Hope this helps, let us know if you find any difficulty executing this.
Best Regards.

5

I’m getting “Object not set to reference” in assign activity.

Can you please share your code ? I want to know:

  1. what is being done in Build data activity
  2. In (From row In dt.AsEnumerable()
    Group row By colValue = row(“Col1”).ToString() Into grp = Group
    Select dtUpdated.LoadDataRow(New Object(){
    colValue,
    grp.Count()
    },False)
    ).CopyToDataTable
    What is dt.AsEnumerable ?
6

@Yugal_Raju

→ Build data table designs the future data to be stored in the specified format. Since the data you read has one column & you need 2 columns in the output data table, this will give you the dt structure.

->In simple terms, AsEnumerable() helps you convert data table into set of data rows, on which you can perform specified operations. You can get more info from the following thread:

Hope this helps,
Best Regards.

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.