Conditional format formula to look for duplicates in multiple columns

[sonosite]

I am using excel for making out a shift schedule for my company. How do I create a conditional format formula to look for duplicate initials in multiple columns and to repeat the assessment independently for many more rows?

Additionally, the formula would only look at the first 2 characters in a cell to check for duplication.


Example of data


Site1 Site2 Site3 Site4 Site5
1/1/2015 AA BB CC DD EE
1/2/2015 BB CC BB AA EE
1/3/2015 CC CC CC BB AA
1/4/2015 BB CC BB-t AA EE


First row has unique initials of AA to EE. There is no error with this row.
Second row has duplicated BB in two columns that I want the formula to highlight.
Third row has duplicated CC in three columns that I want the formula to highlight.
Fourth row has duplicated BB in two columns when you look at the first two characters in the cell that I want the formula to highlight.


Since this is a day by day basis, creating a conditional formating for each row individually would be unrealistic. The formula needs to look at every single row independently in the spreadsheet.

Thanks.

 

[skywriter]

Here's a solution I came up with if you want to try it.

I selected the data below starting with A1 and ending in F2 so that A1 is the cell that is the active cell.

I then go to conditional formatting and choose to use a formula to decide which cells to conditionally format.

I then enter the formula you see repeated below, but only one time is necessary.

=COUNTIF(1:1,A1)>1

When the cells are are selected as I described above and the formula is entered as you see if will highlight the duplicate cells.

There is one caveat in this solution, the formula creates an intentional circular reference, I don't know of another formula to do what you want. Maybe someone else does.

You have to allow iterations in order for the formula to work with the circular reference.

I have Excel 2010 and I go to file, options, formula and then check the box that says allow iterative calculations.

I have the box for maximum iterations set to 1000 and this seems to be plenty.

If you do not have Excel 2010 you can try searching help for iterative calculations, iterations, or maybe circular references on your copy of Excel to see where the setting is.

The conditional formatting doesn't show up on my computer when I save my post, but it is there and it's highlighting the duplicates in each row.


Sheet14

*	A	B	C	D	E	F
1	EE	BB	EE	DD	EE	FF
2	KK	KK	GG	AA	LL	EE

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format A1 1. / Formula is =COUNTIF(1:1,A1)>1 Abc B1 1. / Formula is =COUNTIF(1:1,A1)>1 Abc C1 1. / Formula is =COUNTIF(1:1,A1)>1 Abc D1 1. / Formula is =COUNTIF(1:1,A1)>1 Abc E1 1. / Formula is =COUNTIF(1:1,A1)>1 Abc F1 1. / Formula is =COUNTIF(1:1,A1)>1 Abc A2 1. / Formula is =COUNTIF(1:1,A1)>1 Abc B2 1. / Formula is =COUNTIF(1:1,A1)>1 Abc C2 1. / Formula is =COUNTIF(1:1,A1)>1 Abc D2 1. / Formula is =COUNTIF(1:1,A1)>1 Abc E2 1. / Formula is =COUNTIF(1:1,A1)>1 Abc F2 1. / Formula is =COUNTIF(1:1,A1)>1 Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​
1​		Site1​	Site2​	Site3​	Site4​	Site5​
2​	01/01/2015​	AA​	BB​	CC​	DD​	EE​
3​	01/02/2015​	BB​	CC​	BB​	AA​	EE​
4​	01/03/2015​	CC​	CC​	CC​	BB​	AA​
5​	01/04/2015​	BB​	CC​	BB-t​	AA​	EE​

Select B2:F5 being B2 the active cell (the one that is not shaded after the selection)

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=SUMPRODUCT(--(LEFT($B2:$F2,2)=LEFT(B2,2)))>1

pick the format you want (Font color = red in the example above)

Hope this helps

M.

 

[skywriter]

I take back what I said about the circular reference.
If the formula was in one of the actual cells it would cause a circular reference, it works fine in conditional formatting and you don't have to select the iterations.

 

[sonosite]

Oh, wow.... Thank you so much. It works! However, is there a way to conditionally avoid considering multiple blank cells as "duplicates" if I use background color formating?

Maybe something like this

	A​	B​	C​	D​	E​	F​
1​		Site1​	Site2​	Site3​	Site4​	Site5​
2​	01/01/2015​	AA​	BB​	CC​	DD​	EE​
3​	01/02/2015​	BB​	CC​	BB​	AA​	EE​
4​	01/03/2015​	CC​	CC​	CC​	BB​	AA​
5​	01/04/2015​	BB​	CC​	BB-t​	AA​	EE​

<tbody>
</tbody>

Select B2:F5 being B2 the active cell (the one that is not shaded after the selection)

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=SUMPRODUCT(--(LEFT($B2:$F2,2)=LEFT(B2,2)))>1

pick the format you want (Font color = red in the example above)

Hope this helps

M.

 

[Marcelo Branco]

Oh, wow.... Thank you so much. It works! However, is there a way to conditionally avoid considering multiple blank cells as "duplicates" if I use background color formating?

Try this formula
=AND(B2<>"",SUMPRODUCT(--(LEFT($B2:$F2,2)=LEFT(B2,2)))>1)

M.

 

[sonosite]

Marcelo,

You are incredible. Obrigado!

 

[sonosite]

Since this formula worked perfectly, I would want to ask another conditional formating question, but now in the vertical direction. I don't know how to create one new large table sample, so I am copying/pasting your sample multiple times. Consider it as one big spreadsheet with multiple rows.

How do I create a conditional formula that looks in a single column and highlights if any initials are repeated more than 4 times (>4)?

Sample below:
Column B: no repeated initals more than 4 times. No highlight occurs for column B
Column C: AA is repeated 5 times. Conditional formating should highlight AA
Column D: Conditional formating should only look at the first 2 characters in a cell for repeat. AA is repeated 6 times and is highlighted
Column E: AA is repeated 5 times. BB is repeated 5 times. There are also 5 blank cells. Conditional formating should highlight AA and BB only

Note: for this discussion, I don't care if initials are repeated on the same row. You have solved that already!

	A​	B​	C​	D​	E​	​
1​		Site1	Site2	Site3	Site4
2​	01/01/2015​	AA	AA	AA	AA
3​	01/02/2015​	AA-t	AA	AA-t
4​	01/03/2015​	AA	AA	AA	AA
5​	01/04/2015​	AA-r	AA	AA-r	AA

<tbody>
</tbody>

6	01/05/2015​	BB​	BB​	AA​	AA​	​
7​	01/06/2015​	BB	BB	AA	AA
8​	01/07/2015​	BB	BB	BB
9​	01/08/2015​	CC	AA	CC	BB
10​	01/09/2015​	CC	CC	CC	BB
11​	01/10/2015​	DD	DD	CC	BB

<tbody>
</tbody>

12	01/11/2015​	EE​	EE​	EE​	BB​	​
13​	01/12/2015​	EE	EE	EE
14​	01/13/2015​	EE	EE	EE	BB
15​	01/14/2015​	FF	FF	FF
16​	01/15/2015​	FF	FF	FF
17​	01/16/2015​	FF	FF	FF

<tbody>
</tbody>

 

[Peter_SSs]

.. another option for the original question might be
=AND(B2<>"",COUNTIF($B2:$F2,LEFT(B2,2)&"*")>1)

 

[Peter_SSs]

Since this formula worked perfectly, I would want to ask another conditional formating question, but now in the vertical direction. I don't know how to create one new large table sample, so I am copying/pasting your sample multiple times. Consider it as one big spreadsheet with multiple rows.

How do I create a conditional formula that looks in a single column and highlights if any initials are repeated more than 4 times (>4)?

Excel Workbook
	B	C	D	E
1	Site1	Site2	Site3	Site4
2	AA	AA	AA	AA
3	AA-t	AA	AA-t
4	AA	AA	AA	AA
5	AA-r	AA	AA-r	AA
6	BB	BB	AA	AA
7	BB	BB	AA	AA
8	BB	BB	BB
9	CC	AA	CC	BB
10	CC	CC	CC	BB
11	DD	DD	CC	BB
12	EE	EE	EE	BB
13	EE	EE	EE
14	EE	EE	EE	BB
15	FF	FF	FF
16	FF	FF	FF
17	FF	FF	FF
18
CF Columns

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =AND(B2<>"",COUNTIF(B$2:B$1000,LEFT(B2,2)&"*")>4)	Abc