Formula to find out the top value from a list

[Goddess]

Hi,

I have 3 sheets of data. Sheet 1 is the raw data. Sheet 2 is where I calculate the 'mean'.

Sheet 3 is where I need to present the data. I will need to extract the top 5 'mean' from Sheet 2. Is there any formula to calculate it and return the corresponding text, with the same cell colours?

Copy of 01 Response Summary GC.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Sheet1
2			Anna					Ben					Jim
3		Mean	2	4	6	8	Total	2	4	6	8	Total	2	4	6	8	Total
4	Fruits	Apple	3	4	0	0	7	6	1	0	0	7	3	2	1	0	6
5	Fruits	Grape	2	2	3	0	7	5	2	0	0	7	4	0	2	0	6
6	Fruits	Orange	1	4	2	0	7	3	4	0	0	7	4	0	2	0	6
7	Vegetables	Carrot	1	5	1	0	7	1	5	1	0	7	1	3	1	1	6
8	Vegetables	Leek	1	2	3	1	7	1	2	3	1	7	4	2	0	0	6
9	Vegetables	Spinach	5	2	0	0	7	5	2	0	0	7	4	2	0	0	6
10	Vegetables	Cucumber	4	3	0	0	7	4	3	0	0	7	2	1	2	1	6
11	Meat	Lamb	1	4	2	0	7	4	0	1	0	5	4	1	1	0	6
12	Meat	Chicken	4	3	0	0	7	3	1	1	0	5	6	0	0	0	6
13
14	Sheet2
15			Anna					Ben					Jim
16		Mean	2	4	6	8	Total	2	4	6	8	Total	2	4	6	8	Total
17	Fruits	Apple	6	16	0	0	3.14	12	4	0	0	2.29	6	8	6	0	3.33
18	Fruits	Grape	4	8	18	0	4.29	10	8	0	0	2.57	8	0	12	0	3.33
19	Fruits	Orange	2	16	12	0	4.29	6	16	0	0	3.14	8	0	12	0	3.33
20	Vegetables	Carrot	2	20	6	0	4.00	2	20	6	0	4.00	2	12	6	8	4.67
21	Vegetables	Leek	2	8	18	8	5.14	2	8	18	8	5.14	8	8	0	0	2.67
22	Vegetables	Spinach	10	8	0	0	2.57	10	8	0	0	2.57	8	8	0	0	2.67
23	Vegetables	Cucumber	8	12	0	0	2.86	8	12	0	0	2.86	4	4	12	8	4.67
24	Meat	Lamb	2	16	12	0	4.29	8	0	6	0	2.80	8	4	6	0	3.00
25	Meat	Chicken	8	12	0	0	2.86	6	4	6	0	3.20	12	0	0	0	2.00
26
27	Sheet3
28	Top 5 items
29	Anna	Ben	Jim
30	Leek	Leek	Carrot
31	Grape	Carrot	Cucumber
32	Orange	Chicken	Apple
33	Lamb	Orange	Grape
34	Carrot	Lamb	Orange
35
Sheet1

Cell Formulas
Range	Formula
Q4:Q12,L4:L12,G4:G12	G4	=SUM(C4:F4)
M17:P25,H17:K25,C17:F25	C17	=C4*C$3
G17:G25,Q17:Q25,L17:L25	G17	=SUM(C17:F17)/G4

Thanks!

 

[bebo021999]

top5.xlsm

drive.google.com

Option Explicit
Sub TEST()
Dim lr&, i&, j&, k&, c&, rng, arr(1 To 6, 1 To 6)
Application.ScreenUpdating = False
With Sheets("Sheet2")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    rng = .Range("B4:Q" & lr).Value
    .Range("WW4:WW" & lr).Value = Evaluate("=ROW(A1:a" & lr - 3 & ")")
    For j = 7 To 17 Step 5
        c = c + 1
        .Range("a4:WW" & lr).Sort Cells(3, j), xlDescending
        arr(1, c) = .Cells(2, j - 4).Value
        For k = 2 To 6
            arr(k, c) = .Cells(k + 2, 2).Value
            arr(k, c + 3) = .Cells(k + 2, 2).Interior.Color
        Next
    Next
    .Range("a4:WW" & lr).Sort Cells(3, "WW"), xlAscending
    .Columns("WW").ClearContents
End With
Sheets.Add after:=Sheets(Sheets.Count)
With ActiveSheet
    .Range("A3").Resize(6, 3).Value = arr
    For i = 2 To 6
        For j = 1 To 3
            .Cells(i + 2, j).Interior.Color = arr(i, j + 3)
        Next
    Next
End With
Application.ScreenUpdating = True
End Sub

With sheet2 data display like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Sheet2
2			Anna					Ben					Jim
3		Mean	2	4	6	8	Total	2	4	6	8	Total	2	4	6	8	Total
4	Fruits	Apple	6	16	0	0	3.142857	12	4	0	0	2.285714	6	8	6	0	3.333333
5	Fruits	Grape	4	8	18	0	4.285714	10	8	0	0	2.571429	8	0	12	0	3.333333
6	Fruits	Orange	2	16	12	0	4.285714	6	16	0	0	3.142857	8	0	12	0	3.333333
7	Vegetables	Carrot	2	20	6	0	4	2	20	6	0	4	2	12	6	8	4.666667
8	Vegetables	Leek	2	8	18	8	5.142857	2	8	18	8	5.142857	8	8	0	0	2.666667
9	Vegetables	Spinach	10	8	0	0	2.571429	10	8	0	0	2.571429	8	8	0	0	2.666667
10	Vegetables	Cucumber	8	12	0	0	2.857143	8	12	0	0	2.857143	4	4	12	8	4.666667
11	Meat	Lamb	2	16	12	0	4.285714	8	0	6	0	2.8	8	4	6	0	3
12	Meat	Chicken	8	12	0	0	2.857143	6	4	6	0	3.2	12	0	0	0	2
Sheet2

 

[Goddess]

Hi, thanks! Hmmm... I'm not good in VBA at all

Will give it a try and see how it goes.