Remove duplicate rows in a datagridview based on all the columns in the row

Question

I need to remove duplicate rows in a DataGridView, but all the columns in the rows have to match before it is removed. This is the code I am using. It removes duplicates, but it does not check all the columns in the rows before it removes them. It removes rows that have some of the same data an example lets say rows 1 and  2 have the same data for columns 1 to 10 and 11 and 12 are different it removes 1 of them. I need it to be an exact match before it removes any row.

Dim numberOfRows = DataGridView2.Rows.Count - 1 'subtract the last row which is an editing row
        Dim i As Integer = 0
        While i < numberOfRows - 2

            For ii As Integer = (numberOfRows - 2) To (i + 1) Step -1
                If DataGridView2.Rows(i).Cells(0).Value.ToString() = DataGridView2.Rows(ii).Cells(0).Value.ToString() Then
                    DataGridView2.Rows.Remove(DataGridView2.Rows(ii))
                    numberOfRows -= 1
                End If

            Next
            i += 1
        End While

Answer 1

Hello,

The following will do a grouping on the DataGridView rows by the two columns in the DataGridView then push duplicates into a List(Of DataGridViewRow) and if there are items in the list, remove one from the list and then remove the remaining rows from the DataGridView. 

The key is that each column that needs to be in the compare must be in the GroupBy.

Will work with VS2013 and higher.

Public Class Form1
    ''' <summary>
    ''' There are two colums created in the IDE so make sure 
    ''' if you try this code sample you have defined two columns
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim rows As New List(Of DataGridViewRow)

        Dim query = DataGridView1.Rows.OfType(Of DataGridViewRow)() _
                .Where(Function(row) Not row.IsNewRow) _
                .GroupBy(Function(row)
                             Return New With
                                    {
                                        Key .FirstName = row.Cells("Column1").Value,
                                        Key .LastName = row.Cells("Column2").Value
                                    }
                         End Function
                             ) _
                .Select(Function(group) New With
                        {
                            Key .Value = group.Key,
                            Key .Count = group.Count(),
                            Key .Rows = group.ToList()}) _
                .OrderByDescending(Function(x) x.Count) _
                .ToList

        For Each item In query
            If item.Count > 1 Then
                item.Rows.RemoveAt(0)

                For Each row In item.Rows
                    rows.Add(row)
                Next
            End If
        Next
        If rows.Count > 0 Then
            ' remove the duplicates
            For Each row As DataGridViewRow In rows
                DataGridView1.Rows.Remove(row)
            Next
        End If
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.Rows.Add(New Object() {"Karen", "Payne"})
        DataGridView1.Rows.Add(New Object() {"Karen", "Jones"})
        DataGridView1.Rows.Add(New Object() {"Bill", "Smith"})
        DataGridView1.Rows.Add(New Object() {"Mary", "Adams"})
        DataGridView1.Rows.Add(New Object() {"Mary", "Adams"})
        DataGridView1.Rows.Add(New Object() {"Karen", "Payne"})
        DataGridView1.Rows.Add(New Object() {"Mary", "Adams"})
    End Sub
End Class

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 2

Hello,

The following will do a grouping on the DataGridView rows by the two columns in the DataGridView then push duplicates into a List(Of DataGridViewRow) and if there are items in the list, remove one from the list and then remove the remaining rows from the DataGridView. 

The key is that each column that needs to be in the compare must be in the GroupBy.

Will work with VS2013 and higher.

Public Class Form1
    ''' <summary>
    ''' There are two colums created in the IDE so make sure 
    ''' if you try this code sample you have defined two columns
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim rows As New List(Of DataGridViewRow)

        Dim query = DataGridView1.Rows.OfType(Of DataGridViewRow)() _
                .Where(Function(row) Not row.IsNewRow) _
                .GroupBy(Function(row)
                             Return New With
                                    {
                                        Key .FirstName = row.Cells("Column1").Value,
                                        Key .LastName = row.Cells("Column2").Value
                                    }
                         End Function
                             ) _
                .Select(Function(group) New With
                        {
                            Key .Value = group.Key,
                            Key .Count = group.Count(),
                            Key .Rows = group.ToList()}) _
                .OrderByDescending(Function(x) x.Count) _
                .ToList

        For Each item In query
            If item.Count > 1 Then
                item.Rows.RemoveAt(0)

                For Each row In item.Rows
                    rows.Add(row)
                Next
            End If
        Next
        If rows.Count > 0 Then
            ' remove the duplicates
            For Each row As DataGridViewRow In rows
                DataGridView1.Rows.Remove(row)
            Next
        End If
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.Rows.Add(New Object() {"Karen", "Payne"})
        DataGridView1.Rows.Add(New Object() {"Karen", "Jones"})
        DataGridView1.Rows.Add(New Object() {"Bill", "Smith"})
        DataGridView1.Rows.Add(New Object() {"Mary", "Adams"})
        DataGridView1.Rows.Add(New Object() {"Mary", "Adams"})
        DataGridView1.Rows.Add(New Object() {"Karen", "Payne"})
        DataGridView1.Rows.Add(New Object() {"Mary", "Adams"})
    End Sub
End Class

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Thank you. That worked for me. I had to use the number of the column instead of the "Coilumn1"

Key .FirstName = row.Cells(0).Value,