Keep Count of the domain appeared in emails

Hi Everybody,

I have Excel File Like This :


What I want to do is count which domain has appeared how many times in the sheet, basically what I mean is the output I need should look like this in the image which i have pasted below


as from the output you can see there are 2 email Id’s so the count in the output sheet is 2

same for gmail, hotmail and yahoo the domain has appeared only once, so therefore the count in the output sheet is 1 respectively

Hope you have understood my problem

Any help is appreciated :slight_smile:

1 Like

you can split using @ to get the domain name and add all the domain names to a new datatable

datatable.DefaultView.ToTable(True,“Column Name of new datatable ”) will give you all the unique domain names

loop through unique domain names and use filter datatable in the original datatable and pass unique domain names.

filtereddt.Rows.count will give you the count for each domain

try this

ishan.xlsx (10.8 KB)
test12334.xaml (13.0 KB)



1 Like

prepare an empty datatable within the target result structure - dtResult:

Then use an assign activity:
dtResult =

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("Recipients").toString.Trim.ToLower().Split("@"c)(1) into grp=Group
Let ra = new Object(){k, grp.Count}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

thisis the new version that handles one row containing more than 1 recipient.
Excel.xlsx (63.5 KB)
test12334.xaml (15.3 KB)

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