Count Number of Occurrences in a Column

Help Activities
, ,
1

Hi All,

I am using two excel files i.e., CM and FCCM

In CM I have a Column “Customer No.” and it has a 6 character Alpha-Numeric value.
In FCCM, I have a column name “Internal Account” and has a alpha-numeric value of length 13.

I have assigned the following:

Assign:
row(“Internal Account”) = row(“Internal Account”).ToString.Substring(5,10)

The above assignment will store 6 character in row(“Internal Account”).

Then I took “Lookup Data Table” and want to count the number of occurrences of 6 characters in the CM “Customer No.” field. This CM field has also 6 characters value and want to lookup in row(“Internal Account”).

image
image995×479 12.7 KB

2

Hi @Dr_Raza_Abidi_PhD

Try out this syntax to count number of occurrence
Count_Var(int 32 type)

Count_Var=(
From row In DT
Where row(“Customer No”).ToString=row(“Internal Account”).ToString.Substring(5,10)
Select row
).Count

Thanks
Robin

2 Likes
3

Hi @Dr_Raza_Abidi_PhD,

  1. Inside For each row,
    Assign a variable InternalAccount = row(“Internal Account”).ToString.Substring(5,10)

  2. Use Filter data table activity, input dtCM variable and create dtRequired variable. Provide the condition as Column Name “Customer No.” = InternalAccount variable
    image

  3. Assign intCount = dtRequired.Rows.Count

  4. Write Line - intCount.ToString

Thanks!

4

Thanks @Robinnavinraj_S : But where should I paste this code? In the Lookup Data Table?

5

No you can use this code in assign activity

6

Where DT is your input datatable

7

Okay got it @Robinnavinraj_S :

image
image1470×710 33.2 KB

8

Hi @Dr_Raza_Abidi_PhD

You have used the assign activity inside the for each activity, just use this activity outside the for each

9

Hi @Dr_Raza_Abidi_PhD

refer the below image for more understanding
image

1 Like
10

@Robinnavinraj_S : When I use the assign activity outside the for each loop, it gives me an error that “Customer No.” does not belong to the data table.

11

Can you share your flow

12

Hi @Dr_Raza_Abidi_PhD ,

When we Deal with Excel or datatable values, we can usually adopt the 3 Most Required Data for the Problem Definition to be as Simple as Possible.

  1. Input Data/ Table /Screenshot of the Table/ Excel file (Sample). If there are two Input data, then two tables/excel files.

  2. The Operation to be Performed.(Count Occurrences by Comparing Two DT’s I suppose.)

  3. Expected Output Data (Screenshot/Excel file).

If these are Provided at the earliest, our community can solve the problem or Suggest a Solution real quick.

1 Like
13

@supermanPunch : Yes, indeed, you are right. I have also given the screenshot for the issue and two to three community members also replied me. I am preparing the sample excel files to further understand my problem.

Many thanks,

1 Like
14

@supermanPunch : Pl. find attached the two excel files Book1 and Book2. Actually I want to vlookup the value from Book1 to Book2. If matches so count the occurrences of Book1 value in Book2 Value. Book2 value has the column which has to be extracted through substring function then match the corresponding value in Book1.

It is the same working as in excel with CountIF formula.

Book1.xlsx (8.1 KB)
Book2.xlsx (11.0 KB)
Book3_Output_Result.xlsx (8.2 KB)

Main.xaml (11.9 KB)

15

@Dr_Raza_Abidi_PhD ,

Please do follow the Below Steps :

  1. Let’s say the Primary Steps of reading the file are already known and are done.
    So Book1.xlsx and Book2.xlsx are read using Read Range Activity and assigned Datatable variables. Let the variables be DT1 and DT2.

  2. Now, we can use an Assign Activity to Clone the DT1 as it is similar to Output required as shown in below image.
    image
    OutputDT is also a variable of Type Datatable

  3. Next Use Add data column Activity to Add "Book2 Count" Column to OutputDT
    image

  4. Now, we can Loop through the DT1 datatable value and Count the number of occurrences of values in the DT2 datatable.

  • Use For Each Row Activity with DT1 as the Input
  • Inside For Each Row, Use an Assign Activity to Calculate the Number of Occurrences using a Linq Expression as below
DT2.AsEnumerable.Where(Function(x)x("Internal Account").ToString.Trim.Substring(4,6).ToUpper.Equals(CurrentRow("Customer No.").ToString.Trim.ToUpper)).Count

The Screenshot of the Assign is shown below , where count is a variable of Type Integer
image

  • Next use Add Data Row Activity to add the Customer No. and It’s Number of Occurrences Stored in count variable to OutputDT as shown in the image below:
    image

  • The For Each Activity Should look like the Below Image :
    image

  1. Outside the For Each Loop, Use WorkBook Write Range Activity, and use the OutputDT as the datatable variable like below.

image

This Sequence of Steps should help you achieve the Required Output. Let us know if you Still face issues.

2 Likes
16

Hats off to your efforts man! @supermanPunch

2 Likes
17

Thanks a lot @supermanPunch for your hats off effort but pl. find below the screenshot, I actually want to insert the Lookup Data Table instead of Add Data Row. But in the Lookup Column under the Column Name, which lookup column name should I mention? Thanks,

image
image465×633 16 KB

18

@Dr_Raza_Abidi_PhD ,

According to the Output file that you have Provided, you would have to generate a Table / Datatable which includes the Customer No.'s with it’s Corresponding Number of Occurrences.

I do not see why the LookUp datatable is Required.

Please See the Below Info on Lookup Datatable:

image
image773×323 18.6 KB

19

@supermanPunch : Yes, indeed, I want to add a new column in the existing sheet of excel file and then past the count of occurrences of “Customer No.” after matching through another excel file which you have shared me the LINQ code. In fact, I want to add a new column in Book1.xlsx not in the Output.xlsx. I have just shared Output.xlsx as a sample that shows the required output. So, this output I want to incorporate in Book1.xlsx.

Many thanks,

20

@Dr_Raza_Abidi_PhD ,

In that case, We can just Change the Output File in Write Range Activity. You can change the filepath to the Book1.xlsx path and also the Sheetname to the Sheet1 as it is present in the file.