VBA code or formula for ranking values and then copying text above it in rank order

[Peakey123]

VBA code or a formula for the following please:

I have the words "Li" "Ma" "Ar" "Sp" "Ms" Ch" in the columns C, D, E, F, G and H respectively. They are in row 61 of the excel sheet. I then have numbers underneath each for about 20 rows. I need a formula or VBA code that looks at each row starting at row 62 and ranks "Li" "Ma" "Ar" "Sp" "Ms" Ch" from highest value to lowest depending on the figure beneath it and rank them in columns K,L,M,N and O in the same row as the values.

Thanks for your help

 

[Marcelo Branco]

Could you provide a small data sample (3~5 rows) along with expected results?

M.

 

[Peakey123]

Li	Ma	Ar	Sp	Ms	Ch		1st	2nd	3rd	4th	5th
34	33	34	30	27	40			Ch	Li	Ar	Ma	Sp
37	36	35	33	28	41			Ch	Li	Ma	Ar	Sp
40	39	38	33	31	44			etc	etc	etc	etc	etc
41	40	41	34	34	47
44	43	44	35	35	50

<tbody>
</tbody><colgroup><col span="4"><col><col span="4"><col><col span="3"></colgroup>

 

[Marcelo Branco]

Maybe this...

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

61​

Li​

Ma​

Ar​

Sp​

Ms​

Ch​

1st​

2nd​

3rd​

4th​

5th​

6th​

62​

34​

33​

34​

30​

27​

40​

Ch​

Li​

Ar​

Ma​

Sp​

Ms​

63​

37​

36​

35​

33​

28​

41​

Ch​

Li​

Ma​

Ar​

Sp​

Ms​

64​

40​

39​

38​

33​

31​

44​

Ch​

Li​

Ma​

Ar​

Sp​

Ms​

65​

41​

40​

41​

34​

34​

47​

Ch​

Li​

Ar​

Ma​

Sp​

Ms​

66​

44​

43​

44​

35​

35​

50​

Ch​

Li​

Ar​

Ma​

Sp​

Ms​

<tbody>
</tbody>

Array formula in K62 copied across till P62 and down
=INDEX($C$61:$H$61,MATCH(LARGE($C62:$H62-(COLUMN($C62:$H62)-COLUMN($C62)+1)/(10^5),COLUMNS($K62:K62)),$C62:$H62-(COLUMN($C62:$H62)-COLUMN($C62)+1)/(10^5),0))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Peakey123]

hi M,

thanks for your help with this, unfortunately at the minute when I paste the above into the cell it just says #Value. Any help would be great thanks

 

[Peakey123]

Sorry M,

I have the array working but currently it just returns Ch for everything, so:
Ch Ch Ch Ch Ch
Ch Ch Ch Ch Ch
etc

 

[Marcelo Branco]

You are welcome. Glad to help

M.