none
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


    • Edited by VBShaper Monday, March 20, 2017 11:51 PM
    Monday, March 20, 2017 11:46 PM

Answers

  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by VBShaper Tuesday, March 21, 2017 4:46 PM
    Tuesday, March 21, 2017 12:16 AM
    Moderator

All replies

  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by VBShaper Tuesday, March 21, 2017 4:46 PM
    Tuesday, March 21, 2017 12:16 AM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    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,

     
    Tuesday, March 21, 2017 4:48 PM