How to merge duplicate rows and add values of other columns

Help Activities
1

Hi,

I am getting stuck when trying to merge duplicate rows whilst adding the data of other columns together.

here is an example of what I am trying to achieve:

Before

image

After

City location time
Sydney 123 Street 11 September 2021 12:18pm to 12:23pm , 05 September 2021 12:18pm to 12:23pm
Melbourne 999 Avenue 12 September 2021 9am to 9:10am

Any ideas?

1 Like
2

@Falcao
Welcome to the forum

we can do it with a grouping the data on 1 or more particular columns.

For a first introduction have a look here:

1 Like
3

Hi @Falcao

Please try this,

  1. Read the excel sheet data in the datatable variable name dt.

  2. Create a new variable of type datatable and variable name dt2.

  3. In assign give dt2 = dt.clone

  4. In write range activity give sheet name as “output” and datatable as below,

(From dtRow in dt.asenumerable
Group dtRow by k = dtRow("City").tostring.trim into grp = Group
Let val = string.join(Environment.newline,grp.select(function(d) d("time").tostring).toarray)
Select dt2.rows.add({grp(0)(0),grp(0)(1),val})).copytodatatable

Thanks

3 Likes
4

Thanks! this worked perfectly

5

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