• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Conditional Format duplicate rows with mismatched record

VetJunkie

New Member
I have employee records where an employee can have more than one row based on his rate, I want to format the rows where the employee Id is not unique to the employee
if the id is duplicated but his last name and first name are not the same as the duplicate ID to highlight the mismatched records. picture example Shows a mismatched rows in row 2 and row 8 ; records in row 5 and 6. Thanks for your help in advance
62052
 

Attachments

Last edited:

bosco_yip

Excel Ninja
Select A2:D11 >> Conditional Formatting >> New rule >> click "use a formula…...", in the formula rule enter :

=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)<>COUNTIF($A:$A,$A2)

Then, click Format >> choose cell background color in yellow >> OK

Regards
Bosco
 

VetJunkie

New Member
Select A2:D11 >> Conditional Formatting >> New rule >> click "use a formula…...", in the formula rule enter :

=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)<>COUNTIF($A:$A,$A2)

Then, click Format >> choose cell background color in yellow >> OK

Regards
Bosco
Thank you very much, It worked. You are truly Awesome
 

Peter Bartholomew

Well-Known Member
Playing with dynamic arrays again!

62084

id.name.occurences is simply a list of first and last names associated with the current employee id
id.names.distinct is a list of distinct combinations of first and last name
id.names.count is a count of such distinct name combinations

Conditional formatting is applied where
= id.names.count > 1
 

VetJunkie

New Member
Playing with dynamic arrays again!

View attachment 62084

id.name.occurences is simply a list of first and last names associated with the current employee id
id.names.distinct is a list of distinct combinations of first and last name
id.names.count is a count of such distinct name combinations

Conditional formatting is applied where
= id.names.count > 1
Sorry, but you lost me. I don't get it
 

Peter Bartholomew

Well-Known Member
There are two or three points at which I may easily have lost you, I can only guess which apply.
The first is that I use Excel Tables that allows me to use structured references in place of the traditional A1-style of referencing. This allows the column of employee IDs to be referred to as
= Table1[Employee ID]
and an individual ID within the same record becomes
= Table1[@[Employee ID]]

This would allow @bosco_yip's solution to be presented in the form
= COUNTIFS(
Table1[[Employee ID]], Table1[@[Employee ID]],
Table1[First Name], Table1[@[First Name]],
Table1[Last Name], Table1[@[Last Name]] )
<>
COUNTIFS( Table1[[Employee ID]], Table1[@[Employee ID]] )

That is far longer but it can be read without referring to the worksheet and looking up the content of cells. It also does not change whenever Table1 is moved.

The next point at which I may have left you is in my use of defined Names to store and evaluate formulas. Thus I could take the entire formula and, using Name Manager, define a Name 'multiple.names?' that refers to it. The conditional format would then be based upon
= multiple.names?

The final point is that I have used new functions that, as yet, are only available in some (Dynamic Array) versions of Office 365.

Altogether this offers a style of Excel programming that makes no concessions to the knowledge or ability of the end user. The point is that because 'everyone knows how to write a spreadsheet' it doesn't make it the only (or even the best) way. There is no harm in being aware of other options even if you choose not to adopt them.
 
Top