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

Help
1

Dears,

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

Thanks

2 Likes
2

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
3

@hsendel

Try this LINQ query.

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

4 Likes
4

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
5

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

2 Likes
6

Perfect!!! Thanks Palaniyappan

1 Like
7

Cheers @hsendel

8

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 ?

9

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
https://activities.uipath.com/docs/filter-data-table
Cheers @hsendel

10

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

11

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

12

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

13

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

14

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

15

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

16

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

17

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
18

Any suggestion?

19

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

20

Yes please. Thanks

1 Like