Get data from excel but in unique way

Hi ,

I have a excel which is having column called “Issue ID” and “Name”.

I want all issue id against a unique name. eg.
Sushant with all issuer id mentioned in front of his name
Siddhesh with all issuer id mentioned in front of his name

means values in column “Name” should not repeat.
eg. Sushant shows all his “Issue id” at a single time
test.xlsx (37.5 KB)

@sshitol

Can you show a sample output please

cheers

sushant- 10009990, 100005 ← this are the issuer id’s
siddhesh- 1000000

like this

@sshitol

Please use this

Say your read all the data from excel into datatable dt then build a dataatble with two columns one for name and one for ids(use build datatable)

Now use assign as below

 Newdt = (From d In Dt.AsEnumerable()
Group d By k=d("Name").toString.Trim Into grp = Group
Let k1 = String.Join(",",grp.Select(function(x) x("Issue ID").Tostring).ToArray)
Let ra = New Object(){k,k1}
Select r = Newdt.Rows.Add(ra)).CopyToDataTable()

is it not possible can we perform below operation directly on excel instead of building new data table

Newdt = (From d In Dt.AsEnumerable()
Group d By k=d(“Name”).toString.Trim Into grp = Group
Let k1 = String.Join(“,”,grp.Select(function(x) x(“Issue ID”).Tostring).ToArray)
Let ra = New Object(){k,k1}
Select r = Newdt.Rows.Add(ra)).CopyToDataTable()


Hi @Shhitol

You can refer to the workflow for solution.

IssuerID.xaml (8.2 KB)