An alphabetically sorted list of findings...

[JemyM]

I have been building on a converter between two systems. The goal hav
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 somethin
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
separate list of cells gathered from this list, searching for
specific word, such as "Knowledge:" "Craft:" and "Performance:". Th
"hits" should build up separate smaller lists, filling them up from to
down, leaving empty cells (or 0, or a word) at the bottom.

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

For example, I have decicated cells for up to eight "Knowledge".
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

 

[Biff]

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(),"")

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

 

[Guest]

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]

Are the sub lists necessary? Could you use the advanced filter
(Data/Filter/Advanced Filter - check unique records only) to create
unique
list of all of the skills. Then enter a formula to the right of eac
skill
to find the highest value for that particular skill from your origina
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 value
for
each skill in your original table, and cell E2 is the first cell o
the
unique record list (which I created with advanced filter).

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

I am not adept in using the Advanced Filter yet. The complete list i
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, onl
listing skills that begins with a specific word: Craft (max 6)
Performance (max 2), Language (max 3), Knowledge (max 3) and Pilot (ma
3).

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

 

[JemyM]

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.