Get all duplicates and add to single column in datatable

[PJ_17]

Hi,
I’m having trouble working out a way in which to transpose the following data. I’ve got a spread sheet with over 1000+ rows which looks like this

Name | Area

Adam | London
Joey | Cardiff
Jane | London
Alex | Cardiff
Tim | Liverpool
John | London

And i need to tidy it up to group by Area:

Area | Name
London| Adam ; Jane; John
Cardiff | Joey ; Alex
Liverpool | Tim

I’ve tried looping through the data & writing to another cell, but it takes an excessive amount of time to process within the nested loop - is there a better, more efficient way to do this?

[PJ_17]

Solved it: For anyone looking to do something similar - i’ve just gone with this:

For Each row in dt1
Assign strName = row(1).toString (your column that you want to compare)
Within the For Each use an ‘If’ activity and use the following condition (linq):
(From n In dt2.Select() Where n(“Name of Column in 2nd dt”).ToString.ToLower.trim.Equals(strName.toString.ToLower.Trim)Select n).ToArray.Count > 0

In the ‘Then’ section of the if condition, create a variable called dtMatches of type DataTable and assign it to use the same linq above, but rather than end it with ‘.ToArray.Count > 0’ , just use .CopyToDataTable() so it looks like this:
(From n In dt2.Select() Where n(“Name of Column in 2nd dt”).ToString.ToLower.trim.Equals(strName.toString.ToLower.Trim)Select n).CopyToDataTable()

Still within the 'Then section, beneath the assign, add another for each & use dtMatches as the input Datatable in the parameters. Within this for each, create a variable of type String e.g. strAreasCovered = strAreasCovered.toString +" , "+row(insert the column number).toString

*note, the column number is the index of the column thats duplicated. In my case it was row(4).toString

You also need to set the default value of your variable to “” else you receive an error about the string not being initialised.

Other than the above, just use a ‘Add Data Row’ activity beneath this in the ‘Then’ with the ArrayRow in the properties set to {strName.toString , strAreasCovered.toString}

Hope this helps someone.