Top 10 in descending order

[Esradekan]

I need what is probably a simple formula.
I know filtering will do it, but this is from an unattended workbook,
so a formula would be best.

A1:A40 = range named "clients"
B1:B40 = range called "selection"
C1:C10 = result.

A1:A40 is client list
B1:B40 is quantity ordered
C1:C10 is required in order of largest to smallest, top ten, names
only, not amounts

TYIA
Esra

 

[Max]

A1:A40 is client list

B1:B40 is quantity ordered

In C1: =IF(B1="","",B1-ROW()/10^10)
In D1: =INDEX(A:A,MATCH(LARGE(C:C,ROW()),C:C,0))
Select C11, copy down to D40. Hide away col C. Col D returns an auto-sort
of clients in descending order by qty ordered. Any clients with ties (ie
same qty ordered) will be listed in the same relative order that they appear
within A1:A40. Just read-off the top xx as needed.

---

 

[Esradekan]

In C1: =IF(B1="","",B1-ROW()/10^10)
In D1: =INDEX(A:A,MATCH(LARGE(C:C,ROW()),C:C,0))
Select C11, copy down to D40. Hide away col C. Col D returns an auto-sort
of clients in descending order by qty ordered. Any clients with ties (ie
same qty ordered) will be listed in the same relative order that they appear
within A1:A40. Just read-off the top xx as needed.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik








- Show quoted text -

no, that doesnt work. anything i am doing wrong?
TIA
Esra

 

[MartinW]

Hi Esra,

What doesn't work? And what are you doing that is different
to Max's solution?

It works perfectly in my trials and is also a very clever
way to address the problem.

Regards
Martin

 

[Esradekan]

Hi Esra,

What doesn't work? And what are you doing that is different
to Max's solution?

It works perfectly in my trials and is also a very clever
way to address the problem.

Regards
Martin







- Show quoted text -

Only change i have made to first example is change where it says A:A
to $A$1:$A$40 as there is other data below where this is, same wih
C:C, changed to read $C$1:$C$40

TIA
Esra

 

[Esradekan]

Only change i have made to first example is change where it says A:A
to $A$1:$A$40 as there is other data below where this is, same wih
C:C, changed to read $C$1:$C$40

TIA
Esra- Hide quoted text -

- Show quoted text -

oh by the way, i get #NUM in cells in d column.

 

[Esradekan]

oh by the way, i get #NUM in cells in d column.- Hide quoted text -

- Show quoted text -

i got it, your right, it works
and of course you would be right, lol

Thanks
Esra

 

[Max]

i got it, your right, it works

Good to hear that

and of course you would be right ..

In this instance, it's fine. But I'm mindful that as a responder, being
human, there could be occasions where typos/errors might have crept in the
response given which somehow escaped detection

---

 

[Peo Sjoblom]

For future posts please snip the text when you bottom post so people don't
have to scroll pages to see your answer


--

Regards,

Peo Sjoblom