How To Count the Total Number of rows that contains a Keyword in Excel

Dears,

How To Count the Total Number of rows that contains a Keyword in Excel?

Thanks

2 Likes

buddy @hsendel

–use excel application scope and pass the file path as input
–use read range and get the output as a variable named out_dt of type datatable
–then use a for each row loop and pass the above variable as input to the loop
–inside the for each row loop use a IF condition and mention the condition you want
like this
row(“Columnname”).ToString.Contains(“keyword you want to search”)
if this condition gets passed it will go to THEN part of if condition
inside the THEN part use a assign activity to increment a variable count_row of type int32 declared in variable panel of default value 0, like this
count_row = count_row+1

finally this count_row will give us the count of rows which has the keyword buddy
Cheers @hsendel

2 Likes

@hsendel

Try this LINQ query.

int count = inputDT.AsEnumerable().Where(Function(row) row(“ColumnName”).ToString.Contains("Required Key word”)).ToArray().Count

1 Like

Hello Palaniyappan,

I followed your recommendation and still get an error .Please check attachementsBook1.xlsx (10.4 KB) CountRowsContainsWord.xaml (10.1 KB)

1 Like

here you go buddy @hsendel
hope its resolved
CountRowsContainsWord.xaml (11.7 KB)
Kindly try this and let know buddy
Cheers @hsendel

2 Likes

Perfect!!! Thanks Palaniyappan

1 Like

Cheers @hsendel

Last Question: In case I want to delete all rows where there is only one item how to proceed in this case for “B” Letter ?

you can use filter datatable activity buddy where you can remove the rows which has the value “B” in it
for more info you can see


Cheers @hsendel

Thanks but I still can’t delete all rows that have values <=1.
Please find the attachmentsBook1.xlsx (10.4 KB) CountRowsContainsWord.xaml (45.2 KB) for more details.
In case I need to open new Ticket for this issue please let me know.
Thanks in Advance

so here you want to delete or what was the issue buddy @hsendel

Yes, I want to check if any cell in first row has value <=1 than I will delete complete row. Thx in advance

then you can use filter datatable activity buddy
where you can pass the input datatable and mention the columnname as string between quotes and condition as <= and value as 1
and enable the remove radio button and get the output datatable without those rows
Cheers @hsendel

Sorry by <=1 I mean the occurrence of word is 1 or 0, than we delete the row. Thx

i didn’t get you buddy
kindly come again @hsendel

Let me explain more:
I have excel sheet with one column (I already share the example), I want to check this column and in case there is duplicated values I will keep them but in case there is only single values, I will remove it’s related row.Thx

To show by example, how to remove non-duplicated cells and keep others, in attached excel I need to read the column and I find only cell with “B” value so I remove only this row and keep all the remaining ones,.ThxBook1.xlsx (10.4 KB)
Note: This column is dynamic, means values are changing based an another activity

1 Like

Any suggestion?

aah sorry i was working on it, then i dont know how i left that as it is
now let me work on it and get back to you
Cheers @hsendel

Yes please. Thanks

1 Like