I have been building on a converter between two systems. The goal have
been to find which twelve "Skills" is followed by the highest value,
and then create a list out of the top twelve. It may look something
like this:

Climb
Heal
Craft: Electronics
Listen
Performance: Dance
Craft: Art
Knowledge: Physics
Drive
Knowledge: History
Diplomacy
Sleight of Hand
Knowledge: Geology
Hide
Move Silently

What would help me a great deal would be if I somehow could build a
separate list of cells gathered from this list, searching for a
specific word, such as "Knowledge:" "Craft:" and "Performance:". The
"hits" should build up separate smaller lists, filling them up from top
down, leaving empty cells (or 0, or a word) at the bottom.

Theese new, smaller and sorted lists will then be used for other
formulas.

For example, I have decicated cells for up to eight "Knowledge". I
would love if I can get the end result to something like:

1=Knowledge: Geology
2=Knowledge: History
3=Knowledge: Physics
4=
5=
6=
7=
8=

The list for "Craft" shoud be a list of 6:

1=Craft: Art
2=Craft: Electronics
3=
4=
5=
6=

2 for "Performance"

1=Performance: Dance
2=

You should have got the deal by now...

Can this be done in Excel at all?
Would be really happy if someone could help me out.

My english is not 100% so ask me to redefine if needed.


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

Hi!

You can use the same basic technique that was suggested in your other post
from a few days ago: "A list of the 8 highest results".

You would need to change this formula: (from Max)

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

To something like:

=IF(ISNUMBER(SEARCH(A1,B2)),ROW(),"")

Whe A1 = Knowledge (or whatever keyword you want to search)

The only drawback with this method is that you would need the above "helper"
formula in a separate column for each of the different keywords to be
extracted.Then you would need to "point" the Index formula to the helper
column that corresponds to a particular keyword.

Tinker around with it and see if you can get it to work. If you can't just
post back.

Biff

"JemyM" wrote in
message ...

I have been building on a converter between two systems. The goal have
been to find which twelve "Skills" is followed by the highest value,
and then create a list out of the top twelve. It may look something
like this:

Climb
Heal
Craft: Electronics
Listen
Performance: Dance
Craft: Art
Knowledge: Physics
Drive
Knowledge: History
Diplomacy
Sleight of Hand
Knowledge: Geology
Hide
Move Silently

What would help me a great deal would be if I somehow could build a
separate list of cells gathered from this list, searching for a
specific word, such as "Knowledge:" "Craft:" and "Performance:". The
"hits" should build up separate smaller lists, filling them up from top
down, leaving empty cells (or 0, or a word) at the bottom.

Theese new, smaller and sorted lists will then be used for other
formulas.

For example, I have decicated cells for up to eight "Knowledge". I
would love if I can get the end result to something like:

1=Knowledge: Geology
2=Knowledge: History
3=Knowledge: Physics
4=
5=
6=
7=
8=

The list for "Craft" shoud be a list of 6:

1=Craft: Art
2=Craft: Electronics
3=
4=
5=
6=

2 for "Performance"

1=Performance: Dance
2=

You should have got the deal by now...

Can this be done in Excel at all?
Would be really happy if someone could help me out.

My english is not 100% so ask me to redefine if needed.


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

Are the sub lists necessary? Could you use the advanced filter
(Data/Filter/Advanced Filter - check unique records only) to create a unique
list of all of the skills. Then enter a formula to the right of each skill
to find the highest value for that particular skill from your original table

{=MAX(($A$2:$A$8=E2)*($B$2:$B$8))}

where $A$2:$A$8 is the original table, $B$2:$B$8 contains the values for
each skill in your original table, and cell E2 is the first cell of the
unique record list (which I created with advanced filter).

Note the formula is an array formula. When you key it in, you have to hit
Cntrl+Shift+Enter (Excel will put the braces around it - you don't key those
in yourself). Then copy it down your unique list and sort the list to find
the top 12.




"JemyM" wrote:


I have been building on a converter between two systems. The goal have
been to find which twelve "Skills" is followed by the highest value,
and then create a list out of the top twelve. It may look something
like this:

Climb
Heal
Craft: Electronics
Listen
Performance: Dance
Craft: Art
Knowledge: Physics
Drive
Knowledge: History
Diplomacy
Sleight of Hand
Knowledge: Geology
Hide
Move Silently

What would help me a great deal would be if I somehow could build a
separate list of cells gathered from this list, searching for a
specific word, such as "Knowledge:" "Craft:" and "Performance:". The
"hits" should build up separate smaller lists, filling them up from top
down, leaving empty cells (or 0, or a word) at the bottom.

Theese new, smaller and sorted lists will then be used for other
formulas.

For example, I have decicated cells for up to eight "Knowledge". I
would love if I can get the end result to something like:

1=Knowledge: Geology
2=Knowledge: History
3=Knowledge: Physics
4=
5=
6=
7=
8=

The list for "Craft" shoud be a list of 6:

1=Craft: Art
2=Craft: Electronics
3=
4=
5=
6=

2 for "Performance"

1=Performance: Dance
2=

You should have got the deal by now...

Can this be done in Excel at all?
Would be really happy if someone could help me out.

My english is not 100% so ask me to redefine if needed.


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

JMB Wrote:
Are the sub lists necessary? Could you use the advanced filter
(Data/Filter/Advanced Filter - check unique records only) to create a
unique
list of all of the skills. Then enter a formula to the right of each
skill
to find the highest value for that particular skill from your original
table

{=MAX(($A$2:$A$8=E2)*($B$2:$B$8))}

where $A$2:$A$8 is the original table, $B$2:$B$8 contains the values
for
each skill in your original table, and cell E2 is the first cell of
the
unique record list (which I created with advanced filter).

Note the formula is an array formula. When you key it in, you have to
hit
Cntrl+Shift+Enter (Excel will put the braces around it - you don't key
those
in yourself). Then copy it down your unique list and sort the list to
find
the top 12.

I am not adept in using the Advanced Filter yet. The complete list is
about 68 skills followed by a value.

Bluff 5
Climb 40
Computer Use 0
Concentration 1

By a previous formula the top 12 skills are selected by it's value.
Skills below thoose 12 and their values are then forgotten.

Now I just need to build five lists based upon thoose selected 12, only
listing skills that begins with a specific word: Craft (max 6),
Performance (max 2), Language (max 3), Knowledge (max 3) and Pilot (max
3).

All theese tables exist in a separate stylesheet so it's ok if they
doesnt look nice, as long as they work.


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

JMB Wrote:
Are the sub lists necessary? Could you use the advanced filter
(Data/Filter/Advanced Filter - check unique records only) to create a
unique
list of all of the skills. Then enter a formula to the right of each
skill
to find the highest value for that particular skill from your original
table

{=MAX(($A$2:$A$8=E2)*($B$2:$B$8))}

where $A$2:$A$8 is the original table, $B$2:$B$8 contains the values
for
each skill in your original table, and cell E2 is the first cell of
the
unique record list (which I created with advanced filter).

Note the formula is an array formula. When you key it in, you have to
hit
Cntrl+Shift+Enter (Excel will put the braces around it - you don't key
those
in yourself). Then copy it down your unique list and sort the list to
find
the top 12.

A bit bulky ofcourse but it works as intended. :) I have used it for
all my lists now.


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