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

-
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
Question
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
- Marked as answer by VBShaper Tuesday, March 21, 2017 4:46 PM
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
- Marked as answer by VBShaper Tuesday, March 21, 2017 4:46 PM
-
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,