0

Column A of my spreadsheet has the following data with only values of A, B, C, D and E (shown below). There are more columns that I like to sort with, and would like the first column to highlight the FIRST 5 (not Top 5) of each different class. So when i sort column B, column A (shown below) would highlight the first 5 "A"s, first 5 "B"s, etc.

When I sort from column C, column A will change, and conditional formatting will highlight a new set of first 5 "A"s, "B"s, etc.

I hope I explained it well enough. To clarify, I have added a * next to the first 5 A's, 5, B's, etc.

Class
B*
D*
A*
C*
C*
D*
C*
C*
C*
E*
A*
E*
D*
C
B*
B*
A*
A*
A*
A
D*
D*
C
E*
D
D
B*
B*
C
D
D
B
A
B
C
D
C
D
D
D
D
B
A
B
C
B
B
A
B
C
B
B
C
E*
C
E*
C
D
D
A
A
A
A
A
A
A
A
A
A
A
A
A
A
B
B
B
B
B
B
B
B
B
B
B
B
B
C
C
C
C
C
C
D
D
D
D
D
D
E
E
E
E
E
E
E
E
E
E
E
E
E
E
E
E
1
  • I hope I explained it well enough. Not really. You have given us a column of letters are a word description of what you want, which is vague. – StainlessSteelRat May 9 at 15:57
0

Select the column of data in which you want to highlight the first five entries of each possible value, then add this conditional format (watch the $ signs carefully). Example for column A, substitute A for B, C etc for different columns:

=COUNTIF($A$2:$A2,$A2)<6

This counts how many rows so far up to and including the current row have the same value as this one, and checks if that is fewer than 6. More efficient than counting whole columns and does not care about sort order at all, so as you add data and move it around, sort on other columns etc it still just works.

1
  • This seemed to do exactly what i was hoping. Much appreciated, thank you. – zippy42167 May 15 at 2:07
0

This may work for you:

:Caveat:

  • I've tried the below show method with all possibilities, but it may differ in a few cases.

enter image description here

How it works:

  • First you have to SORT columns in Ascending order.

  • Then select rows except Header Values.

  • Finally apply this Formula as New Rule.

    =COUNTIFS($A:$A,$A30,$B:$B,$B30)>=5

  • Apply appropriate cell/fill color.

  • Adjust cell references in the formula as needed.

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.