## Highlight more than once taken course in any given day

**Question:**My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any employee is free to take a plethora of different courses and they could take them all on the same day if they want. What I am trying to do is see if any employee has taken any course more than once in any given day?

**Answer:**

Conditional formatting formula:

### Explaining Conditional formatting formula

#### Step 1 - Count record

The next COUNTIF function counts values based on a condition or criteria.

COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*COUNTIF($C2, $C$2:$C$35)

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

#### Step 2 - Multiply arrays

All three arrays must return 1 in order to indicate a row as duplicate, to accomplish this we need to use AND logic. AND logic is performed using the asterisk charcater between arrays, in other words, arrays are multiplied.

COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35)

becomes

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0; 0; 1; 0; 1; 0; 0; 0; 0; 0; 1; 0; 0}*{1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}*{1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 1; 1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0}

and returns

{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

#### Step 3 - Sum numbers in array

The SUM function adds numbers in a cell range or array and returns a total.

SUM(COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35))

becomes

SUM({1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 1.

#### Step 4 - Check if record is a duplicate

If total is larger than 1 then we know it is a duplicate, the larger than character allows us to compare the total to a given condition, the returned value is a boolean value, TRUE or FALSE.

SUM(COUNTIF($A2, $A$2:$A$35)*COUNTIF($B2, $B$2:$B$35)*COUNTIF($C2, $C$2:$C$35))>1

becomes

1>1 and returns FALSE. Cell A2 is not highlighted.

### Get Excel *.xls file

Highlight duplicate online classes using conditional formatting

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

### One Response to “Highlight more than once taken course in any given day”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Oscar so I modified your formula to look for duplicates and highlight a table of 4100 records to columns a, aa, ac as follows-

SUM(COUNTIF($A2, $A$2:$A$5000)*COUNTIF($AA2, $AA$2:$AA$5000)*COUNTIF($AC2, $AC$2:$AC$5000))>1

but didn't work ugh.. can you assist pls?