Merge multiple rows in one

[ravindar.singh (Rav Singh)]

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

[Gokul001 (Gokul Balaji)]

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

[Sanjit_Pal]

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

[ravindar.singh (Rav Singh)]

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

[Gokul001 (Gokul Balaji)]

Kindly share the Sample excel file @ravindar.singh

[ravindar.singh (Rav 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)

[Gokul001 (Gokul Balaji)]

HI @ravindar.singh

Check out the XAML file

DataScrappingInternetDown.xaml (10.1 KB)

Output

Regards
Gokul

[Gokul001 (Gokul Balaji)]

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

[ravindar.singh (Rav Singh)]

@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

[Gokul001 (Gokul Balaji)]

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

[ravindar.singh (Rav Singh)]

Okay Thanks for your help you are amazing.

[ravindar.singh (Rav Singh)]

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

[Gokul001 (Gokul Balaji)]

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

[ravindar.singh (Rav Singh)]

It worked thanks again

[gokul1904 (Glory 01)]

Is this a javascript code?

[Gokul001 (Gokul Balaji)]

No @gokul1904

[gokul1904 (Glory 01)]

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

Thanks