vb.net - compare two datagridview tables by specific column

Question

Good day!
I need to compare two DataGridView tables on a specific column and display the matches in datagridview3 and the discrepancies in datagridview4.
So far, that's all I have done. I can display in the message:
Tell me how to change the code.

For Each rw1 As DataGridViewRow In DataGridView1.Rows
    For Each rw2 As DataGridViewRow In DataGridView2.Rows
        If rw1.Cells(3).Value = rw2.Cells(3).Value Then
            If rw1.Cells(3).Value IsNot Nothing Then
                MsgBox(rw1.Cells(3).Value & "  in dgv2")
            End If
        End If
    Next
Next

data in table 1:

Surname	Name	address	id
Surname1	Name1	Adress1	14526
Surname2	Name2	Adress2	75856
Surname3	Name3	Adress3	36514
Surname4	Name4	Adress4	78425
Surname5	Name5	Adress5	36178
Surname6	Name6	Adress6	98317

question from:https://stackoverflow.com/questions/65843609/compare-two-datagridview-tables-by-specific-column

Answer 1

Using Linq Linq to do the heavy lifting of finding matching and non-matching entries, it then befores a matter of looping through the cells and setting the values in the target DataGridViews

Dim dgv1Rows As List(Of DataGridViewRow) = DataGridView1.Rows.Cast(Of DataGridViewRow).ToList()
Dim dgv2Rows As List(Of DataGridViewRow) = DataGridView2.Rows.Cast(Of DataGridViewRow).ToList()

Dim matchingItems = dgv1Rows.Where(Function(x)
                                        Return dgv2Rows.Any(Function(y) x.Cells(3).Value = y.Cells(3).Value)
                                    End Function)

Dim notMatchingItems = dgv1Rows.Where(Function(x)
                                            Return Not dgv2Rows.Any(Function(y) x.Cells(3).Value = y.Cells(3).Value)
                                        End Function)

For Each rw1 As DataGridViewRow In matchingItems
    Dim addedRowId As Integer = dgvMatching.Rows.Add()
    Dim targetRow As DataGridViewRow = dgvMatching.Rows(addedRowId)
    For Each cell As DataGridViewCell In rw1.Cells
        targetRow.Cells(cell.ColumnIndex).Value = cell.Value
    Next
Next

For Each rw1 As DataGridViewRow In notMatchingItems
    Dim addedRowId As Integer = dgvNotMatching.Rows.Add()
    Dim targetRow As DataGridViewRow = dgvNotMatching.Rows(addedRowId)
    For Each cell As DataGridViewCell In rw1.Cells
        targetRow.Cells(cell.ColumnIndex).Value = cell.Value
    Next
Next

This simply loops through all the cells in the DataGridViewRow setting the value in the appropriate target (dgvMatching or dgvNotMatching) DataGridViewRow. It will work for any number of cells/columns.