Search for column value and merge data cells

I have two excel sheets where one has duplicate ids with 1 more column and the other has only unique ids with 1 extra column. I want to merge them by searching for those unique ids from the second datatable in the first datatable. I have uploaded a sample file for better understanding.merge.xlsx (9.8 KB)

1 Like

@Dominic @ovi @arivu96

Hi @jamnanin

Refer this post.
DataTable filtering with expressions


1 Like

Does anyone know how do i select rows from one table that are not available in the other table.


let us take you want to get the rows from dt1 which are not there in dt2

(From p in dt1.Select()
where (From q in dt2.Select() where string.Join(“,”,q.ItemArray).Equals(string.Join(“,”,p.ItemArray)) Select q).ToArray.Count<1
Select p).ToArray.CopyToDataTable()


Hi @jamnanin,

I don’t know you got the solution for merge cells. But here it has solution to merge and un-merge cells.


Could you give me any idea?
Concat the second column value if the first column value is same
for example:
Number Data
007 Email Flow
007 Test Bug 53306
007 Title 1119
007 Title Test
007 test bug
009 1156
089 Title 21173
098 test Doc Section

I want like below.
Number Data
007 Email Flow,Test Bug 53306,Title 1119,Title Test,test bug
009 1156
089 Title 21173
098 test Doc Section

I would like to know Query like that.below is just sql query
SQL> select tracking_num,
2 listagg(title_of_doc_sec,‘,’) within group (order by title_of_doc_sec) title_of_doc_sec
3 from (select distinct tracking_num , title_of_doc_sec from some_table)
4 group by tracking_num;

Hi @Khine_Nyo

Let us take you are having a datatable as dta.
Let us take dtb as Output DataTable

Use this Query in Assign Activity

dtb=(From p In dta.Select 
Group p By Name=p("Number").ToString Into GroupA=Group
Select dta.Rows.Add(GroupA(0)("Number").ToString,String.Join(",",GroupA.Select(Function(x) x("Data")).ToList))).ToArray.CopyToDataTable

After this use WriteRange and right in Excel sheet. You will get the required Output.


1 Like