An alphabetically sorted list of findings...

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

--
Jemy
-----------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2694
View this thread: http://www.excelforum.com/showthread.php?threadid=46870

0
9/19/2005 2:55:59 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
288 Views

Similar Articles

[PageSpeed] 24

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

"JemyM" <JemyM.1vl5me_1127099120.6128@excelforum-nospam.com> wrote in 
message news:JemyM.1vl5me_1127099120.6128@excelforum-nospam.com...
>
> 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?action=getinfo&userid=26945
> View this thread: http://www.excelforum.com/showthread.php?threadid=468703
> 


0
biffinpitt (3172)
9/19/2005 4:39:12 AM
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?action=getinfo&userid=26945
> View this thread: http://www.excelforum.com/showthread.php?threadid=468703
> 
> 
0
jmb (271)
9/19/2005 4:50:02 AM
JMB Wrote:
> 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

--
Jemy
-----------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2694
View this thread: http://www.excelforum.com/showthread.php?threadid=46870

0
9/19/2005 2:53:51 PM
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?action=getinfo&userid=26945
View this thread: http://www.excelforum.com/showthread.php?threadid=468703

0
9/19/2005 5:50:12 PM
Reply:

Similar Artilces:

Master Category List
I there a way I can have everyone Outlook pull the Master Category list from the Exchange server or is this something that must be maintained at each workstation? More info here: http://www.slipstick.com/outlook/olcat.htm -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "M.Siler" <John.Doe@NoSpam.com> wrote in message news:OZdKAmthEHA.3428@TK2MSFTNGP11.phx.gbl... >I there a way I can have everyone Outlook pull the Master Category list >from > the E...

More on Alphabetize
My apologies for a badly written question. I have Excel Workbooks for each of 12 months (e.g. Jan. 2004 Daily Figures). They are in a Folder in My Documents named '2003 Daily Figures', in irregular order. I can Drag each but not Drop it where I wish it. Is there a way to Alphabetize the 12 Excel entries within a My Documents Folder? Sure. Rename them to 01-2004, 02-2004, etc. **** Hope it helps! **** ~Dreamboat Excel VBA Certification Coming Soon! www.VBAExpress.com/training/ www.Brainbench.com Word Test Developer 2000,2002,2003 ******************************** "Old ...

Sort a "range" of sheets.
Hi: I have used spreadsheets to track my stock purchases over more than 20 years. I have tended to add new "sheets" next to a smilar stock so that it was easy to tranfer common information. Anyway, I have somewhat of a mess. I wold like to be able to select a portion of the workbook sheets and sort them alphabetically. How do I do this? Thanks in advance. See http://www.cpearson.com/Excel/sortws.aspx -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "John Gilmer" <gilmer@crosslink.net> wrote in message news:...

Contact List
How come whne I put in a contact in Outlook 2004, and then I add a fax #, it lists the contact twice on my main list? It's because there is no Outlook 2004. The standard behavior for the Outlook Address book is to display all electronic addresses (both fax and e-mail). That behavior cannot be changed. The only way to prevent the display of fax numbers is to store them in a different field or to disguise them (e.g., precede them with an alpha character) so that Outlook won't recognize them as phone numbers. There are also a number of utilities available that can do this for you automat...

Sorting error?
I'm hoping someone can help me with a problem I am having with Excel 2000. I have several columns of data with a header row and want to alpha sort all the data according to the names in column C. I click in column c and click on the sort button. My header row gets sorted along with everything else. If I use the Data>Sort dialog I don't have this problem. This used to work until recently. BTW, I have the header row separated from the rest by a Freeze Pane. There is no empty row under the header. Any idea what gives??? Nick Hi Excel 'guesses' the structure of your data...

Combinning Multiple Lists
I am trying to combine three different lists into one without bein limited to the space between the number of the lists. For example: Fruit Veggie Meat Orange Carrot Beef Pear Eggplant Chicken Apple Broccoli Veal Lemon Cabbage Lime I want the three lists to become one like this: Orange Pear Apple Lemon Lime Carrot Eggplant Broccoli Cabbage Beef Chicken Veal Having quite a hard time on doing this. The real hard part is that th lists have variable lengths. And would perfer to not have a limit o the length of the lists. Than...

An alphabetically sorted list of findings...
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:&...

How do you sort words in Excel by the number of letters in a word
I have a list of 5,000 words that I need sort by the number of letters in the word for my students is this possible to do? The words range from 1 letter to 9 letters in a word. with the use of a helper column =LEN(A1) if you do not want to count spaces =LEN(SUBSTITUTE(A1," ","")) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Kinger" <Kinger@discussions.microso...

Drop down lists #2
Can you help me - I am trying to make a drop down box - where a user can select from a list of information what will go in the box -- my list is in a sheet labeled sheet and I labled the table in the name box as "Standards" --- I went to my master data base highlighted the row I want the drop down box to appear - then I used the --- DATA - VALIDATION - LIST - and input the name STANDARDS into the source box and clicked enter -- now instead of getting the list when I check the box -- I get the word STANDARDS in my drop down box - can you help? Thank you Katherine ...

how does a viewer read a hyperlink list?
I've attached multiple web links to a shape and when I view full screen and click on the shape I see "Hyperlink List" in a box. I've tried several methods of getting to those links with no success. ...

Alphabetize?
With a list of topics by the month (e.g. July 2004 Daily Numbers), how can they be alphabetized? Perhaps one way .. Assuming you have in col A, A1 down July 2004 Daily Numbers July 2004 Weekly Numbers August 2004 Daily Numbers August 2004 Weekly Numbers September 2004 Daily Numbers September 2004 Weekly Numbers etc Put in B1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1),SEARCH(" ",TRIM(A1))+1)+1,99) Copy down Col B will extract the text-string after the "Month Year" string Use col B as the key to sort both cols A and B Alternatively, Try Data > Text to colum...

Body Text paragraphs within a List Style ?
Hopefully I will explain my question clearly. ;) If one has created a list style in MS Word 2008, how does one include "in that list" some paragraphs that are in one's Body Text style but are based on the indent rules of the list style? In other words, I start a list with list paragraph number 1. but after the first sentence I'd like to include several paragraphs with a style consistent with the list paragraph but not a new list number. Hope that is clear. Thanks for any tips. -- Norm Hi Norm: Investigate the "List Continue" series of ...

distribution list has reached the maximum size
I was creating a distribution list from contacts and got the above error message. Is there any way to expand the size I am running exchange 2000. It would only allow 166 addresses in my distribution list. this is the limit for personal distribution lists...if you need a bigger one, it needs to be created by the Exchange admin as a mail-enabled group... "dsikirica" <anonymous@discussions.microsoft.com> wrote in message news:C3F9F9E8-87E2-4056-AA60-EBE9230482A7@microsoft.com... > I was creating a distribution list from contacts and got the above error message. Is there a...

Data List liek autotrader
How would you set up a car slection data list, say exactly lik autotrader or ebay where you can pick make of car, THEN the models of cars pop u depending on the make u select, and the year as well. Basically like your are pickign out a car on a professiona website(just alot fewer cars), how would i go about i -- prelude994 ----------------------------------------------------------------------- prelude994e's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1601 View this thread: http://www.excelforum.com/showthread.php?threadid=27550 You can create dependent d...

alphabet tabs
I am trying to create a draft a dictionary-type document where I would have a tmplate with a letter for each page (i.e. A,B,C....X,Y,Z). Then I could readily select a page where I could add an item or word for a specific letter (i.e. quickly go to M or P or Q) On Tue, 9 Feb 2010 15:31:02 -0800, David Glover <David Glover@discussions.microsoft.com> wrote: >I am trying to create a draft a dictionary-type document where I would have a >tmplate with a letter for each page (i.e. A,B,C....X,Y,Z). Then I could >readily select a page where I could add an item or word for...

Data Validation against table list
Hi, I've got some Excel tables in a spreadsheet and I'd like to add data validation to some other cells so that only those values in the table can be chosen. I know how to do that where the list of choices comes from a named range, but Excel won't let me refer to the table name in the same fashion. Anyone have any ideas? Thanks! hi, 'anonymous' ! > ... I've got some Excel tables in a spreadsheet and I'd like to add data validation to some other cells > so that only those values in the table can be chosen. > I know how to do that where the list of choic...

Custom Lists #4
How do I create a custom list that I can use within an Excel sheet to select from a range of constants? (Like the Pick From List drop down box e.g. In the table below, Applicant No and Name will always be variable, but the Recruiter Name will just be a constant list of staff who deal with the applicants - how do I just get a drop down bow for these recruiters instead of having to type them in Applicant No Applicant Name Recruiter Nam 1 Fred Joh 2 Joan Joh 3 Arthur Steve ...

Pivot Sort
Hi I run WinXP with Excel2000 I have a table with about 70 lines of data. I have created a pivot table for the data (only 2 data columns and a total column) I would like to have the 2nd column in the pivot table sorted. I right click on the 2nd column header of the pivot table and select "field settings" I then select the "advanced" tab I click the Auto Sort option to ascending However, it does not sort the data. I have tried it descending and it is the same result What am I doing wrong? Thanks John Just some thoughts. I don't know about xl2000, but in my...

Custom Contacts Find View
Hi experts! I would like to know if I can create a custom view to find filtered contacts... I have create a new entity related to contacts and when the user click on the look up should appear only some contacts that are related to a specify account. Is there any way to do this??? Thanks and regards!! Pablo -- Pablo Gómez Hi Pablo, You can do it. Try to learn from following URL; http://ronaldlemmen.blogspot.com/2006/12/filter-data-in-crm-lookup-field.html You may contact me directly at my email address if you need any help in this regard. -- PLEASE do click on Yes or No butt...

Some sort of sum product
I have a series of names in a coulmn in Sheet1. I have a range of data in sheet2 that looks like this. Name1 DataType1 DataType2 Name2 DataType1 DataType2 Name3 DataType1 DataType2 Name1 DataType1 DataType2 Name3 DataType1 DataType2 Name2 DataType1 DataType2 Name1 DataType1 DataType2 I need to add up coulmn B in Sheet2 and coulmn C in Sheet2 and divide B for each name in Sheet1. There are multipule instances of a name in sheet2 coulmn A and I need to add each instance in coulmn B and coulmn C before dividing. Any tips? =SUMIF(Sheet2!A:A,A1,Sheet2!B:B)+SUMIF(Sheet2!A:A,A1,Sheet2!C:C) not...

alphabetically paginate in a merge
i'm trying to put a church directory together and have no problem merging from my excel database but i'm wondering if there is some way that i can cause the merge to paginate alphabetically... like in an address book. all the "a's" together, all the "b's" together and so on. any help would be greatly appreciated! What version Publisher? If you are using 2003-07 you could do this manually if you are using the catalogue merge. After the merge, in step five, create a new publication. Arrange the entries the way you are describing. -- Mary Sauer MSF...

How may I make a list display in alphabetical order in Excel?
Data>sort>ascending -- Regards, Peo Sjoblom "pat henry" <pathenry@discussions.microsoft.com> wrote in message news:497E03A1-36FD-4962-B081-A66FC962E884@microsoft.com... > ...

Find/replace fractions
I paste some data into lots of cells and then need to=20 edit/find/replace cells that contain "=BD" or other=20 fractions, substituting the decimal equivalents. The=20 problem is that I can't paste "=BD" into the Edit/Find box=20 in this form. In fact you can't paste anything into this=20 box and if I type in the box "1/2", Excel doesn't=20 see "=BD". Very grateful for any ideas. Many thanks Grayling How did you enter the "�" into your post? Did you maybe use <Alt>0189 ? If you pasted it, and didn't use the above, d...

Distribution Lists #21
I want to send an email to a Distribution List but don't want the members email address to appear in the TO: field. How do I have the Distirbution List name appear in the To: or just the individual name and note the whole list. Thanks This MS KB article may help: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q266339 -- Russ Valentine [MVP-Outlook] "aLAN" <anonymous@discussions.microsoft.com> wrote in message news:0e5701c4ab36$59370c20$a601280a@phx.gbl... >I want to send an email to a Distribution List but don't > want the members email address...

Outlook 2002 views grouping/sorting
I noticed a (irritating) difference between Outlook 2000 and 2002 definition of folder Views. Back in 2000, you could set to group by Conversation Topic, which lumped all emails with the same email subject together. Now it was still sorted by date first, meaning the subject-topic with the most recent received (descending) email with be positioned on top. So it was going by Sort, then Group. Now in 2002, the priority has been reversed. It is grouped first then sorted by date. Meaning the email subject with the first in alphabetical order will ALWAYS be positioned on top, even when its latest ...