Solved

Help highlighting rows with identical columns with same ID column

Posted on 2014-03-08
12
249 Views
Last Modified: 2014-03-10
How do you loop through a DataGridView and based on the same SN values, compare the rows and other columns and if they match highlight the entire row?

For example If I have:

  SN               CTRY      Item
 10411           BEL        BMW
 10411           BEL        BMW
 10421           USA        Jeep
 10451           USA        Jeep

Only the first two rows should be Highlighted (Backcolor Green, Textcolor White) because they both have SN = 10411 and cells for "Item" column in those two rows are the same.

Thanks,

Victor
0
Comment
Question by:vcharles
  • 6
  • 6
12 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39915113
Hi Victor;

This code should only color the complete row of those that identical in all columns.

The query will group the rows by all columns having the same value and return the index of those rows if the group has more then one row.

Dim matchingRows = From row As DataGridViewRow In DataGridView1.Rows _
                   Group row By Key = New With {Key .SN = row.Cells("SN").Value, Key .CTRY = row.Cells("CTRY").Value, _
                                                Key .Item = row.Cells("Item").Value, Key .Item2 = row.Cells("Item2").Value} _
                                                Into Group _
                   Where Group.Count() > 1
                   From g In Group _
                   Select g.Index

For Each r In matchingRows
    DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Green
Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39915225
Hi,

I'm trying to use the code from both solutions on the same grid, but the cells that were previously red remained red, would highlighting the rows on the second solution instead of changing the color to green solve this problem? if yes, How do I highlight the rows instead?

Thanks,

Victor

Dim colorCells = From row As DataGridViewRow In DataGridView1.Rows _
                 Group row By Key = row.Cells("SN").Value Into Group _
                 Where Group.Count() > 1 _
                 Select New With _
                        { _
                            .Idx = Group.Select(Function(idx) idx.Index).ToList(), _
                            .Item = Group.Select(Function(i1) i1.Cells("Item").Value).Distinct().Count(), _
                            .Item2 = Group.Select(Function(i2) i2.Cells("Item2").Value).Distinct().Count() _
                        }

For Each snGroup In colorCells
    For Each idx As Integer In snGroup.Idx
        ' One If statement for each column whos background can change color
        ' Testing Item and Item2. Add one If statement to match the the Item's in Select statement above.
        If snGroup.Item > 1 Then
            DataGridView1.Item(2, idx).Style.BackColor = Color.Red
        End If
        If snGroup.Item2 > 1 Then
            DataGridView1.Item(3, idx).Style.BackColor = Color.Red
        End If
    Next
Next

Dim matchingRows = From row As DataGridViewRow In DataGridView1.Rows _
                   Group row By Key = New With {Key .SN = row.Cells("SN").Value, Key .CTRY = row.Cells("CTRY").Value, _
                                                Key .Item = row.Cells("Item").Value, Key .Item2 = row.Cells("Item2").Value} _
                                                Into Group _
                   Where Group.Count() > 1
                   From g In Group _
                   Select g.Index

For Each r In matchingRows
    DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Green
Next
0
 

Author Comment

by:vcharles
ID: 39915367
Hi,

Is it also possible to include a For Next loop to check the columns values instead of hard coding the columns? I'll will be providing users the option to select columns they want to display, hard coding the columns in the code will cause an error if it's not included in the DataGrid.

Thanks,

Victor
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39915814
Hi Victor;

Try changing this part of the code in your last post:
For Each r In matchingRows
    DataGridView1.Rows(r).DefaultCellStyle.BackColor = Color.Green
Next

Open in new window

To this code.
For Each r In matchingRows
    For idx As Integer = 0 To DataGridView1.Columns.Count - 1
        DataGridView1.Item(idx, r).Style.BackColor = Color.Green
    Next
Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39915908
Hi,

It works.

Thank You.

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39915911
Not a problem Victor, glad to help.
0
 

Author Comment

by:vcharles
ID: 39916959
Hi again,

Is it also possible to include a For Next loop to check the columns values instead of hard coding the columns? I'll will be providing users the option to select columns they want to display, hard coding the columns in the code will cause an error if it's not included in the DataGrid.

Thanks,

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39917483
I guess I'm not understanding your follow-up question can you please explain in more detail.
0
 

Author Comment

by:vcharles
ID: 39917549
Hi,

I'm sorry, I included my follow question on the wrong post. It's in regard to the code below from a previous post:

Is it possible to replace Item and Item2 by i in a For Next Loop and apply the for all the other columns starting from Column 3, I tried it but it didn't work. I'm trying to avoid hard coding the columns incase they are not on the Grid because suers will be able to choose columns they want to display.






Dim colorCells = From row As DataGridViewRow In DataGridView1.Rows _
                 Group row By Key = row.Cells("SN").Value Into Group _
                 Where Group.Count() > 1 _
                 Select New With _
                        { _
                            .Idx = Group.Select(Function(idx) idx.Index).ToList(), _
                            .Item = Group.Select(Function(i1) i1.Cells("Item").Value).Distinct().Count(), _
                            .Item2 = Group.Select(Function(i2) i2.Cells("Item2").Value).Distinct().Count() _
                        }

For Each snGroup In colorCells
    For Each idx As Integer In snGroup.Idx
        ' One If statement for each column whos background can change color
        ' Testing Item and Item2. Add one If statement to match the the Item's in Select statement above.
        If snGroup.Item > 1 Then
            DataGridView1.Item(2, idx).Style.BackColor = Color.Red
        End If
        If snGroup.Item2 > 1 Then
            DataGridView1.Item(3, idx).Style.BackColor = Color.Red
        End If
    Next
Next

Attempt:
Dim i as integer = 3
Dim colorCells = From row As DataGridViewRow In DataGridView1.Rows _
                 Group row By Key = row.Cells("SN").Value Into Group _
                 Where Group.Count() > 1 _
                 For I = 3 to 10
                 Select New With _
                        { _
                            .Idx = Group.Select(Function(idx) idx.Index).ToList(), _
                            .Item = Group.Select(Function(i1) i1.Cells(i).Value).Distinct().Count()
                        }
                    Next
For Each snGroup In colorCells
    For Each idx As Integer In snGroup.Idx
        ' One If statement for each column whos background can change color
        ' Testing Item and Item2. Add one If statement to match the the Item's in Select statement above.
        If snGroup.Item > 1 Then
            DataGridView1.Item(2, idx).Style.BackColor = Color.Red
        End If
        If snGroup.Item2 > 1 Then
            DataGridView1.Item(3, idx).Style.BackColor = Color.Red
        End If
    Next
Next

Instead of 10 how do I get the number of columns?

Thanks,

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39917852
Hi Victor;

This can not be placed in the middle of a Linq query:

For I = 3 to 10

Because it is not part of the Linq query language. You will need to take it out of the query and do the rest through code. The following code sample should work.

Dim dontMatch = From row As DataGridViewRow In DataGridView1.Rows _
        Group row By Key = row.Cells("SN").Value Into Group _
        Where Group.Count() > 1 _
        Select Group

For Each snGroup In dontMatch
    Dim Index As List(Of Integer) = snGroup.Select(Function(idx) idx.Index).ToList()
    For i As Integer = 2 To DataGridView1.ColumnCount - 1
        Dim item = snGroup.Select(Function(i1) i1.Cells(i).Value).Distinct().Count()
        If item > 1 Then
            For Each row In Index
                DataGridView1.Item(i, row).Style.BackColor = Color.Red
            Next
        End If
    Next
Next

Open in new window

If you need the number of columns in the DataGridView you can use the property ColumnCount, for example:

DataGridView1.ColumnCount
0
 

Author Comment

by:vcharles
ID: 39918516
Hi,

It worked!!

THANK YOU!

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39918521
Not a problem.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question