How to Merge two records with one identical column value using Linq or others

Hi,

I would like to ask how can I able to achieve my goal of merging one column values with one another if they have the same value of column1.

to better illustrate:

Current:

Goal:

Hi!

Read the excel output as dt
Take one For ech row
dt.DefaultView.ToTable(False,(“Emp#”).ToString)

Regards,
NaNi

Hey, It’s not giving me the result I wanted to see.

Can you show me the result?

Regards,
NaNi

hi @dhennespiritu,

If the data isn’t huge, you can loop the DT after sorting the column “Emp#”

If the two “Emp#” are same then you can merge the type and remove the 2nd duplicate record as show in goal. And post that add row to different DT.

If not same simply add row to a different DT.

Hope this helps :smiley: :beers:

The result only gave me one column

image

Will you be able to show me how to achieve that? I tried that but when removing the duplicate, it just removed the one that I wanted to keep.

Steps:

  1. Read the Orginal DT
  2. Sort the DT On basis of the emp#
  3. Loop it on Original DT.
  4. For index = 0, store all the DT variable (assuming its small in variables)
  5. For index = 1, check if the emp# for this row item is same with index 0
    Yes:= append the type into the variable
    No:= add the variables into a new DT with all the variables
  6. go on till the end

Hope this helps :slight_smile:

Does the steps 4 and 5 referring to a new datatable?

It is not giving me the expected result.

I already solved the problem and this is the solution:

(From d In dt_Temp1.AsEnumerable
Group d By k1=d("Emp#").ToString.Trim, 
k2=d("Job#").ToString.Trim, 
k3=d("Commence_date").ToString.Trim,  
k4=d("OccupTermDate").ToString.Trim,  
k5=d("Position#").ToString.Trim,
k6=d("EmpStatus").ToString.Trim Into grp=Group
Let n = String.Join(",",grp.Select(Function (x) x("Type").toString.Trim).Distinct())
Let ra = New Object(){k1, k2, k3, k4, k5, k6,n}
Select dt_QueueLoader.Rows.Add(ra)).CopyToDataTable

Thanks for your help!

1 Like

Yup that’s corret

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