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

Regards,
Arivu

1 Like

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

@jamnanin

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()

Regards,
Mahesh

Hi @jamnanin,

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

Regards
Balamurugan.S

Hi
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.

Regards,
Mahesh

1 Like