Using conditional formatting or any other method to find the top 3 of a data set and then the next 3 in the remainder plus extra data

[Taz5678]

I am working on a spreadsheet that needs to identify the top 3 scores in a set number of columns. I have done this using conditional formatting but it is not essential if there is a better way to solve the following problem:

I need to use the remaining scores from the info above plus some additional ones to create the next top 3 but these cannot be the original ones. I need these clearly identified which is why I started with conditional formatting.

Whatever the solution, the formulas etc need to be set up so that changes to the scores can be inputted and therefore the colours change (or however it is identified).

Hope someone can help because I have spent too much time today trying to work this out!

 

[DanteAmor]

Hi @Taz5678 Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following conditional format

Dante Amor
	A
1	scores
2	10
3	24
4	25
5	20
6	24
7	31
8	16
9	30
10	11
11	24
12	16
Hoja6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A12	Expression	=$A2>=LARGE($A$2:$A$12,3)	text	NO

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[kevin9999]

I am working on a spreadsheet that needs to identify the top 3 scores in a set number of columns. I have done this using conditional formatting but it is not essential if there is a better way to solve the following problem:

I need to use the remaining scores from the info above plus some additional ones to create the next top 3 but these cannot be the original ones. I need these clearly identified which is why I started with conditional formatting.

Whatever the solution, the formulas etc need to be set up so that changes to the scores can be inputted and therefore the colours change (or however it is identified).

Hope someone can help because I have spent too much time today trying to work this out!

What happens if you have 2 (or more) scores in say the top 3 that are the same? Do you only want to identify that score once, or as many times as it appears?

 

[kevin9999]

See if this gives you what you want. Change sheet name & number of columns to suit & test on a copy of your workbook.

Option Explicit
Sub Three_Then_Three()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, r As Range
    Set ws = Worksheets("Sheet1")       '<~~ *** Change to actual sheet name ***
    Dim LRow As Long, a As Long, b As Long, i As Long
    
    For i = 1 To 5                      '<~~ *** Change the 5 to the number of columns you want ***
        LRow = ws.Cells(Rows.Count, i).End(xlUp).Row
        Set r = ws.Range(ws.Cells(2, i), ws.Cells(LRow, i))
        With r
            a = WorksheetFunction.Large(WorksheetFunction.Unique(r), 3)
            b = WorksheetFunction.Large(WorksheetFunction.Unique(r), 6)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=" & a
            .FormatConditions(1).Interior.Color = vbYellow
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=" & b, Formula2:="=" & a
            .FormatConditions(2).Interior.Color = vbGreen
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

Before:

Book1
	A	B	C	D	E
1	scores	scores	scores	scores	scores
2	37	56	11	86	84
3	41	94	21	89	81
4	64	59	80	94	83
5	20	94	72	13	79
6	38	97	24	80	21
7	36	45	49	12	99
8	86	87	21	38	55
9	62	17	28	82	59
10	96	80	32	65	63
11	26	77	23	42	34
12	97	62	69	52	29
13	16	32	54	86	31
14	24	89	71	40	73
15	85	85	47	82	41
16	18	26	64	77	31
17	67	21	30	76	34
18	31	58	72	60	48
19	39	92	98	84	40
20	33	89	44	12	89
21	88	73	62	49	41
22	94	18	12	33	21
23	35	80	38	83	63
24	75	73	43	13	65
25	18	49	32	66	40
26	42	51	97	78	12
27	47	49	75	70	13
28	79	58	91	95	94
29	62	71	44	91	85
30	100	20	30	11	63
31	76	24	35	35	39
32	60	49	73	78	28
33	36	70	68	39	14
34	78	83	72	90	79
35	50	76	13	76	73
36	81	36	39	96	42
37	88	92	50	54	51
38	53	77	51	19	60
39	15	92	28	60	83
40	39	67	35	36	39
41	99	21	65	62	87
42	26	39	76	40	89
43	80	49	37	96	75
44	48	14	12	20	58
45	13	65	68	70	53
46	31	30	61	74	56
47	24	40	22	99	74
48	46	20	20	99	95
49	66	25	96	54	13
50	43	60	94	81	15
51	85	15	27	33	96
Sheet1

After:

Book1
	A	B	C	D	E
1	scores	scores	scores	scores	scores
2	37	56	11	86	84
3	41	94	21	89	81
4	64	59	80	94	83
5	20	94	72	13	79
6	38	97	24	80	21
7	36	45	49	12	99
8	86	87	21	38	55
9	62	17	28	82	59
10	96	80	32	65	63
11	26	77	23	42	34
12	97	62	69	52	29
13	16	32	54	86	31
14	24	89	71	40	73
15	85	85	47	82	41
16	18	26	64	77	31
17	67	21	30	76	34
18	31	58	72	60	48
19	39	92	98	84	40
20	33	89	44	12	89
21	88	73	62	49	41
22	94	18	12	33	21
23	35	80	38	83	63
24	75	73	43	13	65
25	18	49	32	66	40
26	42	51	97	78	12
27	47	49	75	70	13
28	79	58	91	95	94
29	62	71	44	91	85
30	100	20	30	11	63
31	76	24	35	35	39
32	60	49	73	78	28
33	36	70	68	39	14
34	78	83	72	90	79
35	50	76	13	76	73
36	81	36	39	96	42
37	88	92	50	54	51
38	53	77	51	19	60
39	15	92	28	60	83
40	39	67	35	36	39
41	99	21	65	62	87
42	26	39	76	40	89
43	80	49	37	96	75
44	48	14	12	20	58
45	13	65	68	70	53
46	31	30	61	74	56
47	24	40	22	99	74
48	46	20	20	99	95
49	66	25	96	54	13
50	43	60	94	81	15
51	85	15	27	33	96
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A51	Cell Value	>=97	text	YES
A2:A51	Cell Value	between 88 and 97	text	YES
B2:B51	Cell Value	>=92	text	YES
B2:B51	Cell Value	between 85 and 92	text	YES
C2:C51	Cell Value	>=96	text	YES
C2:C51	Cell Value	between 80 and 96	text	YES
D2:D51	Cell Value	>=95	text	YES
D2:D51	Cell Value	between 90 and 95	text	YES
E2:E51	Cell Value	>=95	text	YES
E2:E51	Cell Value	between 87 and 95	text	YES

 

[kevin9999]

Whatever the solution, the formulas etc need to be set up so that changes to the scores can be inputted and therefore the colours change (or however it is identified).

To apply the above code automatically, put the following in the worksheet code area of the sheet in question:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim r As Range
    Dim LRow As Long, a As Long, b As Long, i As Long
    
    For i = 1 To 5                      '<~~ *** Change the 5 to the number of columns you want ***
        LRow = Cells(Rows.Count, i).End(xlUp).Row
        Set r = Range(Cells(2, i), Cells(LRow, i))
        With r
            a = WorksheetFunction.Large(WorksheetFunction.Unique(r), 3)
            b = WorksheetFunction.Large(WorksheetFunction.Unique(r), 6)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=" & a
            .FormatConditions(1).Interior.Color = vbYellow
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=" & b, Formula2:="=" & a
            .FormatConditions(2).Interior.Color = vbGreen
        End With
    Next i
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[Taz5678]

Thank you so much for the solutions. I am afraid this is all going over my head - I am not as competent with excel as I need to be. Please can you imagine I am a 3 year old! Also I realise that I said columns in my thread when I meant rows. I think that sums up my level!

 

[Taz5678]

What happens if you have 2 (or more) scores in say the top 3 that are the same? Do you only want to identify that score once, or as many times as it appears?

The 3 that are selected first cannot be duplicated. Thanks

 

[kevin9999]

OK, before I'm willing to progress this any further, I'll need to see your actual data. You can do this by either posting a copy of your sheet using the XL2BB - Excel Range to BBCode, or alternatively, share your file via Dropbox, Google Drive or similar file sharing service.

 

[Taz5678]

OK, before I'm willing to progress this any further, I'll need to see your actual data. You can do this by either posting a copy of your sheet using the XL2BB - Excel Range to BBCode, or alternatively, share your file via Dropbox, Google Drive or similar file sharing service.

Great. Thank you. I shall do that later today as I am off to meetings now. Really grateful!

 

[Taz5678]

OK, before I'm willing to progress this any further, I'll need to see your actual data. You can do this by either posting a copy of your sheet using the XL2BB - Excel Range to BBCode, or alternatively, share your file via Dropbox, Google Drive or similar file sharing service.

Loading Google Sheets

Hi Kevin9999,

I have uploaded the spreadsheet to my google drive, the link is above. To clarify what I need to do:

I need to workout the top 3 from columns T-AM (1) and then the top 3 from columns P, T-AM,AO-BJ (2). I need these highlighted in two different colours and then a total of (1) in column AN and a total of (2) in BK.

Top 3 of (1) takes priority over top 3 of (2) and there can be no duplicates.

I need this to be set up so that changes can be made to the scores and the colours and totals update.

I hope you are able to help.

Thanks