# Assess rows of numbers for duplicates

I have a data table that contains duplicate numbers:

``````    A   B   C   D   E
1   1104.8  1130.1  1124.3  1099.75 1138.85
2   1099.9  1080.5  1082.35 1080    1106.95
3   1072.2  1080    1076.85 1064    1091.7
4   898.75  899 895.85  885.2   902.55
5   1298    1307    1299.75 1287.25 1319
6   1973.05 1968    1962.7  1943.45 1974
7   1731.65 1742    1742.35 1724    1750.05
8   815 831.7   831.7   811.55  831.7
9   537.1   546.65  546.3   533.05  549.2
10  627.4   634 633.35  624.2   634
11  812.5   803.8   803.8   803.8   831
12  986.85  997 1006.05 976 997.8
13  1930.7  1936.5  1938.15 1926.9  1948.8
14  707.2   720 713.75  706 720.2
``````

I'd like to highlight rows that contain 3 of the same number. How can I do this?

## 2 Answers

To highlight rows that contain 3 of the same number do the followings:

• Select A1:D4.
• Go to Conditional Formating.
• Select New Rules & use this Formula.

`=AND(ISNUMBER(A1), COUNTIF(1:1,A1)=3)`

• Apply color format & finish with Ok.

N.B. Adjust Cell & Row references in formula as needed.

• What is wrong with this answer, Y this has been down voted, since it's working smoothly !! – Rajesh S Jul 21 '18 at 12:48

In the next column over in F1, enter the following and fill down:

``````=MAX(COUNTIF(A1:E1,A1),COUNTIF(A1:E1,B1),COUNTIF(A1:E1,C1),COUNTIF(A1:E1,D1),COUNTIF(A1:E1,A1))
``````

The CountIF statements look in turn for matches for each value on the line, then the Max statement says which of the duplicates had the highest count. You'll be able to see the rows with 3 of the same number straight away and then use conditional formatting to highlight yellow if you need.