Excel Automation with count


I’m having an issue in excel automation for the task. I have data in Column-1(Block) and in column-2(Work code) With the data (0204003012/IF/GIS/585362) in this format. Here I need to find the columns contains /GIS/ and in the final output only the count of /GIS/. Please help me with this.

For GIS extraction.xlsx (6.7 MB)

Read range workbook activity - datatable
For each row in datatable -if row(3).contains(“GIS”)
Then increment the counter.so it gives the count.

1 Like

Hi @Ram_Shiva_Reddy

How about this expression

String.join(",",DtRead.DefaultView.ToTable(false, "Work Code").AsEnumerable().Select(Function (a) a(0).ToString).ToArray())

Check out this XAML file

FilterColumnWorkCode.xaml (6.4 KB)



Thank you for your quick response. For the excel sheet that I have attached, For the 1st Block in column-1, need to find the /GIS/ work code count
Block 1- 100
Block 2-150
Just the count.

What do you mean by first block?

Hi @Ram_Shiva_Reddy ,

The Excel file provided by you looks considerably huge. Could you maybe provide a sample data from it ? We could analyse better in that manner. Also Let us know How do you want the Output to be or in which format.