Keep Count of the domain appeared in emails

[Ishan_Shelke]

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 @marico.com 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

[Lak_Ui (Lakshmi)]

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

[jack.chan]

try this

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

input

output

[ppr (Peter)]

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

[jack.chan]

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