Solved

Help with changing cell color for mismatch column based on identical IDs

Posted on 2014-03-07
13
403 Views
Last Modified: 2014-03-08
How do you loop through a DataGridView and based on the same SN values, compare the rows and other columns and if they don't match change the cell colors of the data in unmatched columns to red?

For example If I have:

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

Only the first two rows should be compared because they both have SN = 10411 and cells for "Item" column in those two rows should be red (Text color).

Thanks,

Victor
0
Comment
Question by:vcharles
  • 7
  • 6
13 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39913761
Hi Victor;

A couple of questions.

What is the data source for the data grid view, is it a DataTable object or something else?
Will there be only three columns in the data grid view or more?
Will there be a max of only two rows that can have the same SN or can there be more?
0
 

Author Comment

by:vcharles
ID: 39913817
Hi,

The data is from a DataTable.
There will be more than three column and more than than two rows with the same SN.
Thanks.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39914171
So in the below case the first four rows have the same values in the SN column so which cells in the other two columns get colored Red?

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

Open in new window

0
 

Author Comment

by:vcharles
ID: 39914263
Hi,

It would only be the Item column, but if I had another column "ItemA" where the first four rows were not the same the cells in those rows would also be red.

Victor
0
 
LVL 62

Accepted Solution

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

The following code snippet should meet your requirements as posted in the question. Seeming that the only column that is beeing tested for Distinct values is the Item column and you stateing that there are more so I added another colimn called Item2 so show how to handel multiple columns.

This query will group all rows having the same SN value which I have assumed it to be of type Integer. Then filter out all Group's having a count of 1 leaving only those Group's that have multiple Item's in them. In the Select clause I get the Row index of all the rows in the group and the count of Distinct values in the columns whos background color may change. One value needs to be created for each column whos color may change and so the reason for Item and Item2.

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

Open in new window

0
 

Author Comment

by:vcharles
ID: 39914778
Thank You. I will try it and get back to you.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39914780
OK.
0
 

Author Comment

by:vcharles
ID: 39914817
Hi,


Do I need to import something?

I received error message:

DataGridViewRowCollection is not  Queryable   make sure you are not missing a reference.......

 on line
Dim colorCells = From row As DataGridViewRow In C1Grid1.Rows _


But I don't get the same error for C1Grid1 on the other part of the code.


Thanks,

V.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39914829
Is C1Grid1 the DataGridView control?

Where you implemented the code, is the data table been filled with data and assigned to C1Grid1 so that it is not empty?
0
 

Author Comment

by:vcharles
ID: 39914832
Hi,

I added system.linq to remove the error.
 
will get back to you shortly.

V.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39914834
Very good.
0
 

Author Comment

by:vcharles
ID: 39914863
Hi,

It works Perfectly!

How do I highlight the rows with a perfect match?

Thank You!

Victor
0
 

Author Comment

by:vcharles
ID: 39914883
Hi,

I will ask my last question in a new post.

Thanks again.

Victor
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now