Help required please

[mnut1975]

Hi

I have 2 columns in column A there is a set of numbers and in column B a set of towns.

In column C I would like to a formula to show me top 20 towns based on the value in column A (which Fluctuates) and some of the values may be the same but the town name different. Is this possible? See example below eg.1. As you can see sheffield and slough have the same value and I was trying to use =LARGE(A1:A1001,ROW()) and then =VLOOKUP(P1,$A$1:$M$9999, 2,FALSE) in the next column to give me the town name but when we have duplicate values it picks the first it finds every time so I end with what you see in eg.2


172 London
110 Bolton
96 Manchester
36 Preston
34 Birmingham
33 Rotherham
30 Leicester
22 Sheffield
22 Slough
21 Coventry
20 Bradford
15 Doncaster
15 Leeds
13 High Wycombe
12 Boston
12 Harrow
11 Stockport
9 Burnley
9 Dagenham
9 Dewsbury

eg.2
172 London
110 Bolton
96 Manchester
36 Preston
34 Birmingham
33 Rotherham
30 Leicester
22 Sheffield
22 Sheffield
21 Coventry
20 Bradford
15 Doncaster
15 Doncaster
13 High Wycombe
12 Boston
12 Boston
11 Stockport
9 Burnley
9 Burnley
9 Burnley


Thanks in Advance

 

[T. Valko]

Hi

I have 2 columns in column A there is a set of numbers and in column B a set of towns.

In column C I would like to a formula to show me top 20 towns based on the value in column A (which Fluctuates) and some of the values may be the same but the town name different. Is this possible? See example below eg.1. As you can see sheffield and slough have the same value and I was trying to use =LARGE(A1:A1001,ROW()) and then =VLOOKUP(P1,$A$1:$M$9999, 2,FALSE) in the next column to give me the town name but when we have duplicate values it picks the first it finds every time so I end with what you see in eg.2


172 London
110 Bolton
96 Manchester
36 Preston
34 Birmingham
33 Rotherham
30 Leicester
22 Sheffield
22 Slough
21 Coventry
20 Bradford
15 Doncaster
15 Leeds
13 High Wycombe
12 Boston
12 Harrow
11 Stockport
9 Burnley
9 Dagenham
9 Dewsbury

eg.2
172 London
110 Bolton
96 Manchester
36 Preston
34 Birmingham
33 Rotherham
30 Leicester
22 Sheffield
22 Sheffield
21 Coventry
20 Bradford
15 Doncaster
15 Doncaster
13 High Wycombe
12 Boston
12 Boston
11 Stockport
9 Burnley
9 Burnley
9 Burnley


Thanks in Advance

See this:

http://social.answers.microsoft.com...ce8948a/#0c00b00d-358f-44b5-a649-65c04a4cee90