sort and return column numbers

[rachitprasad]

I am a novice in Macro programming.

Can you please tell me how to sort numeric data in a row and return the
column numbers in the descending order of numbers

Ex-

A B C
15 10 14

i need to get the numbers 1,3,2 as the result.

 

[Guest]

One way

Source numbers assumed in A1:C1
(assuming no ties in the numbers)

Put in A2:
=MATCH(LARGE(1:1,COLUMN()),1:1,0)
Copy across to C2

 

[Domenic]

In case of ties...

A5, copied across:

=MATCH(LARGE($A$1:$C$1-COLUMN($A$1:$C$1)/10^5,COLUMNS($A$5:B5)),$A$1:$C$1
-COLUMN($A$1:$C$1)/10^5,0)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

=MATCH(LARGE($A$1:$C$1-COLUMN($A$1:$C$1)/10^5,COLUMNS($A$5:B5)),$A$1:$C$1
-COLUMN($A$1:$C$1)/10^5,0)

Perhaps the part: COLUMNS($A$5:B5) in the array above
should be replaced by: COLUMNS($A$1:A1) <g> ?

---

 

[Guest]

On reflection, perhaps just use RANK might suffice ..

With source numbers in A1:C1

Put in A2, copied to C2: =RANK(A1,$A$1:$C$1)

Ties in the source numbers, if any, will be given duplicate rankings,
with skipped subsequent rankings

---

 

[Domenic]

Thanks for catching my mistake, Max! I'm glad someone is paying
attention... <VBG>

If entering the formula in A5, and copying it across, the formula should
be as follows...

=MATCH(LARGE($A$1:$C$1-COLUMN($A$1:$C$1)/10^5,COLUMNS($A$5:A5)),$A$1:$C$1
-COLUMN($A$1:$C$1)/10^5,0)