Join 2 data tables by referencing 1 column

[lrtetala (Lakshman Reddy)]

Hello Community!!

I am having an excel with 2 Sheets i want to join the 2 Sheets into a single sheet.
Sheet1:

Sheet2:

Output:

Output (1) (1).xlsx (12.3 KB)

I want Linq query or Invoke Code.

Any suggestions @Yoichi @Anil_G

Thanks in advance!!

[Yoichi]

Hi,

How about the following?

Sample
Sample2023112702L.zip (12.7 KB)

Regards,

[lrtetala (Lakshman Reddy)]

@Yoichi

It is working fine. Is it possible to do with Linq Query or Invoke Code.

[Yoichi]

@lrtetala

Invoke Code.

Can you check the following sample ?

Dim dict As Dictionary(Of String,DataRow)
dict = dt1.AsEnumerable.ToDictionary(Function(r) r(0).ToString,Function(r) r)
Dim previous As String
For Each row  As DataRow In dt2.AsEnumerable()
Dim key As String
If Not String.IsNullOrEmpty(row(0).ToString) Then
    key = row(0).ToString
	previous = row(0).ToString
Else
	key = previous
End If
dtResult.Rows.Add({key,dict(key)("Vendor Name"),dict(key)("Quotation"),dict(key)("Fax"),dict(key)("Email"),dict(key)("Date"),dict(key)("Terms"),row("Item").ToString,row("Part Number").ToString,row("Quantity").ToString,row("UnitPrice").ToString})	
Next

Sample (see Sequence.xaml)
Sample2023112702L (2).zip (14.6 KB)

Regards
,

[lrtetala (Lakshman Reddy)]

@Yoichi

Could you please help me with the given below output as i wanted

[Yoichi]

Hi,

Can you try the following?

Dim dict As Dictionary(Of String,DataRow)
dict = dt1.AsEnumerable.ToDictionary(Function(r) r(0).ToString,Function(r) r)
Dim previous As String
For Each row  As DataRow In dt2.AsEnumerable()
Dim key As String= row(0).ToString
If Not String.IsNullOrEmpty(key) Then
    dtResult.Rows.Add({key,dict(key)("Vendor Name"),dict(key)("Quotation"),dict(key)("Fax"),dict(key)("Email"),dict(key)("Date"),dict(key)("Terms"),row("Item").ToString,row("Part Number").ToString,row("Quantity").ToString,row("UnitPrice").ToString})	
Else
	For Each cname As String In {"Item","Part Number","Quantity","UnitPrice"}
	    dtResult.AsEnumerable().Last().Item(cname)=dtResult.AsEnumerable().Last().Item(cname).ToString+"||"+row(cname).ToString()
	Next
End If
Next

Sample (see Sequence1.xaml)
Sample2023112702L_Windows.zip (16.7 KB)

[lrtetala (Lakshman Reddy)]

Thank you @Yoichi

It is working as expected.