Lookup multiple rows from one datatable in another datatable

[Gidi_automator (Adurota Gideon)]

Hello
I have two datatables A and B with thousands or rows in each
I need to fill a column in A with values in B but I can only pick out those values using a lookup with a different column in A with B.

Basically, I need to lookup a column (e.g. length) from A with a column in B (e.g. breadth). If there are matching values, then I will select the value from another column (height) on the same row in B and copy that value into a different column (height) in A.

I have tried using for each as shown below

Try

Dim length As String
Dim lookupvalue As String
Dim height As String
Dim breadth As String
Dim Bheight As String
Dim Newheight As String

For Each row As DataRow In A.Rows

length = row.item("length").ToString
height = row.item("height").ToString
If String.IsNullOrEmpty(height)Then
	If Not String.IsNullOrEmpty(length) Then
		lookupvalue = length
	Else
	End If

	console.writeline(lookupvalue)
	For Each rowitem As DataRow In B.Rows
		breadth = row.item("breadth").ToString
		Bheight = row.item("Bheight").ToString
		If breadth.contains(lookupvalue) Then
			Bheight = Newheight
		Else
		End If

	Next		
End If
row.item("height") = Newheight		

Next
Catch ex As Exception

errorMessage = ex.Message

End Try

I keep getting the error “column breadth does not belong to table” and then the code breaks.

Thank you in anticipation of your help

[NIVED_NAMBIAR (NIVED N)]

@Gidi_automator

Try

Dim length As String
Dim lookupvalue As String
Dim height As String
Dim breadth As String
Dim Bheight As String
Dim Newheight As String

For Each row As DataRow In A.Rows

length = row.item("length").ToString
height = row.item("height").ToString
If String.IsNullOrEmpty(height)Then
	If Not String.IsNullOrEmpty(length) Then
		lookupvalue = length
	Else
	End If

	console.writeline(lookupvalue)
	For Each rowitem As DataRow In B.Rows
		breadth = rowitem.item("breadth").ToString
		Bheight = rowitem.item("Bheight").ToString
		If breadth.contains(lookupvalue) Then
			 Newheight = Bheight 
		Else
		End If

	Next		
End If
row.item("height") = Newheight		

Next
Catch ex As Exception

errorMessage = ex.Message

End Try

Try this @Gidi_automator

I hope this will work

[AkshaySandhu (AkshaySingh Sandhu)]

yellow highlighted should be “rowitem” instead of row.item , see blue highlighted .

[Gidi_automator (Adurota Gideon)]

Thank you very much. I tried that and it fixed it.

However, I think I may need a more efficient way to achieve this. The datatables contain thousands of rows and it is taking a very long time to run.
Please do you have a better way to achieve this?

Thank you.