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

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

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

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

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

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

But you have the Function translation utility now?

Bob

"JemyM" wrote in
message ...

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
--

Bob Phillips Wrote:
But you have the Function translation utility now?

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


--
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=468387