Merge multiple rows in one

I want to merge matching rows ( excluding one column) in to one. The only difference is lead passenger 1 column.

Input

I want Output like this

Thanks in Advance

HI @ravindar.singh

How about this expression?

(From d In BuildDT.AsEnumerable
Group d By k=d("Booking No").toString.Trim Into grp = Group
Let nj = String.Join(" ",grp.Select(Function (n) n("Leading passenger 1").toString.Trim))
Let ra = New Object(){k,grp.First()(1),grp.First()(2),nj,grp.First()(4)}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

2 Likes

Hey @ravindar.singh,

Use the below code and try.

(From row In dt1.AsEnumerable
        Group row By key = New With { 
        Key.name = row.Item("Name")} Into grp = Group
        Select dt1Clone.LoadDataRow(New Object() {
                key.name,
                String.Join("","",grp.Select(Function(x)x(""Address"").ToString).ToArray)
        }, True)).CopyToDataTable

Thanks,
Sanjit

@Gokul001 .
Thank you for your response. It’s kinda of working but it’s showing the data twice

Kindly share the Sample excel file @ravindar.singh

@Gokul001 Please see the file.
The real data will have multiple columns and multiple rows with different rows. The only columns that would be different is “Lead Passenger 1
(10.3 KB)

HI @ravindar.singh

Check out the XAML file

DataScrappingInternetDown.xaml (10.1 KB)

Output

image

Regards
Gokul

1 Like

Hi @ravindar.singh

Check out the XAML file Based on the Input Excel file

DataScrappingInternetDown.xaml (13.1 KB)

ANR_Passenger_2022_09_06.xlsx (10.8 KB)

Output

Regards
Gokul

@Gokul001 thanks for your help - is there a way where I don’t have to create a output DT. As I might have over 30-40 columns.

Below is my current process

I don’t think so it is possible without creating an DtOutput @ravindar.singh

Okay Thanks for your help you are amazing.

@Gokul001 Actually I ran in to a problem.
Can I group by Booking No and Foreign Source BID?

Hi @ravindar.singh

How about this expression ?

(From d In DtBuild.AsEnumerable
Group d By k=d("Booking No").toString.Trim, k2=d("Foreign Source BID").ToString.Trim Into grp = Group
Let nj = String.Join(" ",grp.Select(Function (n) n("Leading passenger 1").toString.Trim))
Let ra = New Object(){k,grp.First()(1),grp.First()(2),nj,k2}
Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Regards
Gokul

2 Likes

It worked thanks again

Is this a javascript code?

No @gokul1904

Okay . Can you please let me know what type of code that is?

Thanks

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