Join 2 data tables by referencing 1 column

Help Studio
, ,
1

Hello Community!!

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

image
image1491×304 8.32 KB

Sheet2:
image
image884×364 7.41 KB

Output:

image
image1920×348 17.2 KB

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

I want Linq query or Invoke Code.

Any suggestions @Yoichi @Anil_G

Thanks in advance!!

2

Hi,

How about the following?

image
image1215×1361 41.4 KB

Sample
Sample2023112702L.zip (12.7 KB)

Regards,

1 Like
Forum Engagement Daily Reports
3

@Yoichi

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

4

Can you check the following sample ?

image
image505×965 25.9 KB

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
,

1 Like
5

@Yoichi

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

image
image1920×243 10.2 KB

6

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)

1 Like
7

Thank you @Yoichi

It is working as expected.

8

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