Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-07
13
Medium Priority
?
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 64

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 64

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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
 
LVL 64

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 64

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 64

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

Industry Leaders: 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!

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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