A list of the 8 highest results...

I have a stylesheet that contains "knowledges".
The list looks something like:

Knowledges:
Accounting 14
Anthropology 25
Archeology 48
Art 35
Astronomy 59
Biology 124
Chemistry 123
Geology 15
History 28
Law 167
Medicine 69
Natural History 89
Pharmacy 198
Physics 20

Note the value next to each "knowledge". This number is a calculated formula that results in a number between 1-200.

Now, if it's possible, I would like Excel to produce a separate list of the eight knowledges with the highest values right next to it.

Pharmacy
Biology
Chemistry
etc...

Any way this can be done?

Thanks for all replies, if any.

One way, using non-array formulas,
which caters for the possibility of ties in the numbers ..

Assuming table in cols A and B, data from row2 down

Put in D2:
=INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
Copy D2 across to E2

Put in F2: =IF(B2="","",B2-ROW()/10^10)
(Leave F1 empty)

Select D2:F2, copy down to F15

Cols D and E will return the full descending sort of what's in cols A and B
(Col F is the arbitrary tie-breaker)

Just select the desired top 8 from the list within cols D and E
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JemyM" <[email protected]> wrote in message
news:[email protected]...
>
> I have a stylesheet that contains "knowledges".
> The list looks something like:
>
> Knowledges:
> Accounting 14
> Anthropology 25
> Archeology 48
> Art 35
> Astronomy 59
> Biology 124
> Chemistry 123
> Geology 15
> History 28
> Law 167
> Medicine 69
> Natural History 89
> Pharmacy 198
> Physics 20
>
> Note the value next to each "knowledge". This number is a calculated
> formula that results in a number between 1-200.
>
> Now, if it's possible, I would like Excel to produce a separate list of
> the eight knowledges with the highest values right next to it.
>
> Pharmacy
> Biology
> Chemistry
> etc...
>
> Any way this can be done?
>
> Thanks for all replies, if any.
>
>
> --
> JemyM
> ------------------------------------------------------------------------
> JemyM's Profile:
http://www.excelforum.com/member.php...o&userid=26945
> View this thread: http://www.excelforum.com/showthread...hreadid=468387
>

A slightly simpler way, that also allows you to stop at 8

In D2: =INDEX($A$2:$A$15,MATCH(LARGE($B$2:$B$15,ROW(A1)),$B$2:$B$15,0))

and copy down as far as you want

--
HTH

Bob Phillips

"Max" <[email protected]> wrote in message
news:[email protected]...
> One way, using non-array formulas,
> which caters for the possibility of ties in the numbers ..
>
> Assuming table in cols A and B, data from row2 down
>
> Put in D2:
> =INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
> Copy D2 across to E2
>
> Put in F2: =IF(B2="","",B2-ROW()/10^10)
> (Leave F1 empty)
>
> Select D2:F2, copy down to F15
>
> Cols D and E will return the full descending sort of what's in cols A and
B
> (Col F is the arbitrary tie-breaker)
>
> Just select the desired top 8 from the list within cols D and E
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "JemyM" <[email protected]> wrote in
message
> news:[email protected]...
> >
> > I have a stylesheet that contains "knowledges".
> > The list looks something like:
> >
> > Knowledges:
> > Accounting 14
> > Anthropology 25
> > Archeology 48
> > Art 35
> > Astronomy 59
> > Biology 124
> > Chemistry 123
> > Geology 15
> > History 28
> > Law 167
> > Medicine 69
> > Natural History 89
> > Pharmacy 198
> > Physics 20
> >
> > Note the value next to each "knowledge". This number is a calculated
> > formula that results in a number between 1-200.
> >
> > Now, if it's possible, I would like Excel to produce a separate list of
> > the eight knowledges with the highest values right next to it.
> >
> > Pharmacy
> > Biology
> > Chemistry
> > etc...
> >
> > Any way this can be done?
> >
> > Thanks for all replies, if any.
> >
> >
> > --
> > JemyM
> > ------------------------------------------------------------------------
> > JemyM's Profile:
> http://www.excelforum.com/member.php...o&userid=26945
> > View this thread:
http://www.excelforum.com/showthread...hreadid=468387
> >
>
>

BTW, this doesn't handle duplicates as Max's does.

--
HTH

Bob Phillips

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> A slightly simpler way, that also allows you to stop at 8
>
> In D2: =INDEX($A$2:$A$15,MATCH(LARGE($B$2:$B$15,ROW(A1)),$B$2:$B$15,0))
>
> and copy down as far as you want
>
> --
> HTH
>
> Bob Phillips
>
> "Max" <[email protected]> wrote in message
> news:[email protected]...
> > One way, using non-array formulas,
> > which caters for the possibility of ties in the numbers ..
> >
> > Assuming table in cols A and B, data from row2 down
> >
> > Put in D2:
> > =INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
> > Copy D2 across to E2
> >
> > Put in F2: =IF(B2="","",B2-ROW()/10^10)
> > (Leave F1 empty)
> >
> > Select D2:F2, copy down to F15
> >
> > Cols D and E will return the full descending sort of what's in cols A
and
> B
> > (Col F is the arbitrary tie-breaker)
> >
> > Just select the desired top 8 from the list within cols D and E
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > Singapore, GMT+8
> > xdemechanik
> > http://savefile.com/projects/236895
> > --
> > "JemyM" <[email protected]> wrote in
> message
> > news:[email protected]...
> > >
> > > I have a stylesheet that contains "knowledges".
> > > The list looks something like:
> > >
> > > Knowledges:
> > > Accounting 14
> > > Anthropology 25
> > > Archeology 48
> > > Art 35
> > > Astronomy 59
> > > Biology 124
> > > Chemistry 123
> > > Geology 15
> > > History 28
> > > Law 167
> > > Medicine 69
> > > Natural History 89
> > > Pharmacy 198
> > > Physics 20
> > >
> > > Note the value next to each "knowledge". This number is a calculated
> > > formula that results in a number between 1-200.
> > >
> > > Now, if it's possible, I would like Excel to produce a separate list
of
> > > the eight knowledges with the highest values right next to it.
> > >
> > > Pharmacy
> > > Biology
> > > Chemistry
> > > etc...
> > >
> > > Any way this can be done?
> > >
> > > Thanks for all replies, if any.
> > >
> > >
> > > --
> > > JemyM
> >
> ------------------------------------------------------------------------
> > > JemyM's Profile:
> > http://www.excelforum.com/member.php...o&userid=26945
> > > View this thread:
> http://www.excelforum.com/showthread...hreadid=468387
> > >
> >
> >
>
>

Another option is to simply copy cols A and B elsewhere and sort by the
values (descending).
--
Gary''s Student


"JemyM" wrote:

>
> I have a stylesheet that contains "knowledges".
> The list looks something like:
>
> Knowledges:
> Accounting 14
> Anthropology 25
> Archeology 48
> Art 35
> Astronomy 59
> Biology 124
> Chemistry 123
> Geology 15
> History 28
> Law 167
> Medicine 69
> Natural History 89
> Pharmacy 198
> Physics 20
>
> Note the value next to each "knowledge". This number is a calculated
> formula that results in a number between 1-200.
>
> Now, if it's possible, I would like Excel to produce a separate list of
> the eight knowledges with the highest values right next to it.
>
> Pharmacy
> Biology
> Chemistry
> etc...
>
> Any way this can be done?
>
> Thanks for all replies, if any.
>
>
> --
> JemyM
> ------------------------------------------------------------------------
> JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945
> View this thread: http://www.excelforum.com/showthread...hreadid=468387
>
>

Originally Posted by Max

One way, using non-array formulas,
which caters for the possibility of ties in the numbers ..

Assuming table in cols A and B, data from row2 down

Put in D2:
=INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
Copy D2 across to E2

Put in F2: =IF(B2="","",B2-ROW()/10^10)
(Leave F1 empty)

Select D2:F2, copy down to F15

Cols D and E will return the full descending sort of what's in cols A and B
(Col F is the arbitrary tie-breaker)

Just select the desired top 8 from the list within cols D and E

Since the list is sensitive for ties I have tried to go by this version.

After alot of work I finally got it to work...

My major issue was that I was forced to translate the whole thing to Swedish, and I did not see the difference between ROW and ROWS at first. I also managed to write the swedish word for "LARGER" instead of "LARGE" which caused alot of confusion.

It works now and I can therefore continue my work Thanks alot!

But you have the Function translation utility now?

Bob

"JemyM" <[email protected]> wrote in
message news:[email protected]...
>
> Max Wrote:
> > One way, using non-array formulas,
> > which caters for the possibility of ties in the numbers ..
> >
> > Assuming table in cols A and B, data from row2 down
> >
> > Put in D2:
> > =INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
> > Copy D2 across to E2
> >
> > Put in F2: =IF(B2="","",B2-ROW()/10^10)
> > (Leave F1 empty)
> >
> > Select D2:F2, copy down to F15
> >
> > Cols D and E will return the full descending sort of what's in cols A
> > and B
> > (Col F is the arbitrary tie-breaker)
> >
> > Just select the desired top 8 from the list within cols D and E
> >
>
> Since the list is sensitive for ties I have tried to go by this
> version.
>
> After alot of work I finally got it to work...
>
> My major issue was that I was forced to translate the whole thing to
> Swedish, and I did not see the difference between ROW and ROWS at
> first. I also managed to write the swedish word for "LARGER" instead of
> "LARGE" which caused alot of confusion.
>
> It works now and I can therefore continue my work Thanks alot!
>
>
> --
> JemyM
> ------------------------------------------------------------------------
> JemyM's Profile:
http://www.excelforum.com/member.php...o&userid=26945
> View this thread: http://www.excelforum.com/showthread...hreadid=468387
>

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Originally Posted by Bob Phillips

But you have the Function translation utility now?

Well, doesnt help when you misplace "ROW" and "ROWS" :D