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

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
>

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

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

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

Master Category ListI 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 AlphabetizeMy 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 ListHow 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 ListsI 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 wordI 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 #2Can 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 sizeI 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 tabsI 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 listHi, 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 #4How 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 SortHi
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 ViewHi 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 productI 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 mergei'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...

Find/replace fractionsI 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 #21I 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/sortingI 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 ...