Top 10 in descending order
 

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

Top 10 in descending order
 

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 C1:D1, 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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Esradekan" wrote in message
ps.com...
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

Top 10 in descending order
 

On Sep 22, 10:33 pm, "Max" wrote:
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 C1:D1, 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
---"Esradekan" wrote in message

ps.com...



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- Hide quoted text -

- Show quoted text -

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

Top 10 in descending order
 

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" wrote in message
oups.com...
On Sep 22, 10:33 pm, "Max" wrote:
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 C1:D1, 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
---"Esradekan" wrote in message

ps.com...



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- Hide quoted text -

- Show quoted text -

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

Top 10 in descending order
 

On Sep 23, 11:47 pm, "MartinW" wrote:
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" wrote in message

oups.com...



On Sep 22, 10:33 pm, "Max" wrote:
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 C1:D1, 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
---"Esradekan" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -

no, that doesnt work. anything i am doing wrong?
TIA
Esra- Hide quoted text -

- 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

Top 10 in descending order
 

On Sep 24, 7:30 am, Esradekan wrote:
On Sep 23, 11:47 pm, "MartinW" wrote:





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" wrote in message

roups.com...

On Sep 22, 10:33 pm, "Max" wrote:
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 C1:D1, 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
---"Esradekan" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -

no, that doesnt work. anything i am doing wrong?
TIA
Esra- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

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

Top 10 in descending order
 

On Sep 24, 7:59 am, Esradekan wrote:
On Sep 24, 7:30 am, Esradekan wrote:





On Sep 23, 11:47 pm, "MartinW" wrote:

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" wrote in message

roups.com...

On Sep 22, 10:33 pm, "Max" wrote:
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 C1:D1, 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
---"Esradekan" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -

no, that doesnt work. anything i am doing wrong?
TIA
Esra- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

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

Top 10 in descending order
 

"Esradekan" wrote
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Top 10 in descending order
 

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




"Esradekan" wrote in message
oups.com...
On Sep 24, 7:59 am, Esradekan wrote:
On Sep 24, 7:30 am, Esradekan wrote:





On Sep 23, 11:47 pm, "MartinW" wrote:

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" wrote in message

roups.com...

On Sep 22, 10:33 pm, "Max" wrote:
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 C1:D1, 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
---"Esradekan" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -

no, that doesnt work. anything i am doing wrong?
TIA
Esra- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

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