0

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?

enter image description here

-2

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

enter image description here

  • 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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.