How would I sort a list (example provided below) that is located in column A of an
Excel spreadsheet so that I would have all of the words that are two
letters 1st, three letters 2nd, three letters 3rd and so on. Also
after that sort is done I would like to be able to sort each of the
groups of words (2 letter, 3 letters, 4 letters and so on) to be
listed alphabetically within each group. If you have to research the
way to do this then please let someone else handle it, and please only
answer it if you can tell me how to do this correctly the 1st time and
in a simple manner.

aba
abaca
abacist
aback
abacus
abaft
abalone
abamp
abampere
abandon
abandoned
abase
abash
abate
abatement

Thanks,
Joel

Thanks,
Joel

---

Request for Question Clarification by hammer-ga on 25 May 2003 05:32 PDT

Where do you want the sorted list to end up?

Is Column A the only column in the spreadsheet?

Would a VBA routine that performs your task qualify as telling you
"how to do this correctly the 1st time and in a simple manner"?

- Hammer

---

Request for Question Clarification by hammer-ga on 25 May 2003 05:41 PDT

Joel1357,

There is no need to respond to my Request For Clarification. After
seeing your previous question on this issue
( http://answers.google.com/answers/main?cmd=threadview&id=208349 ), I
don't feel that I can fulfill your expectations.

Best regards,

- Hammer

---

Request for Question Clarification by maniac-ga on 25 May 2003 12:33 PDT

Hello Joel357,

Just to make things clear...
 - you do not want an answer that uses cells outside the list
 - you want it "easy to use". For example: select some cells and use a
single menu item or keyboard command to do the sort.
I have a Visual Basic macro that does this (and tested using both your
original and new lists). Would that be an acceptable answer?

If so, please let me know so I can post the macro as an answer along
with an explanation of how to add it to your spreadsheet. If not,
please indicate what other restrictions you may have so we can provide
a proper answer.
  --Maniac

Joel,

As you make clear in your question, this is a two-criterion sort.  You
have to sort by length, and then within each length you sort
alphabetically.

Trouble is, in your one column listing, you only have the words, not
the lengths.  You need to tell Excel how long those words are, it
cannot do it by itself.  Thus you have to add those lengths to the
spreadsheet.  You can do it yourself, manualy, or you can writein a
function/ formula which will do the work automatically.

If your list of words is in column A, you find the lengths by using
the function "=LEN(A1)" in cell B1, and then autofilling the cells
below in column B.  (My preferred method is to highlight all the cells
including cell B1 with the function and then key Control+D.) The
lengths appear in column B.

Then highlight columns A and B, go to Data >> Sort and choose
     Sort by Column B (Ascending)
     Then by Column A (Ascending)
Choose No Header Row and click on OK.

This is the answer to your need, as already been given by secret901
(http://answers.google.com/answers/main?cmd=threadview&id=208349).

It's a pity you don't like secret's answer - but it's what you have to
do to get the results that you want!

By way of illustration, this is what I get using your list (adding a
couple of 2 letter words in the middle of your original list as a
double-check):

an	2
at	2
aba 	4
abaca 	6
aback 	6
abaft 	6
abamp 	6
abase 	6
abash 	6
abate 	6
abacus 	7
abacist 	8
abalone 	8
abandon 	8
abampere 	9
abatement	9
abandoned 	10

I agree with read2live's approach.  I was going to suggest something
slightly different, so that the sort could be achieved with a single
criterion, but in essence it is the same as the comments and answers
you have received up to now.

Perhaps it is the explanation that is causing confusion.  The approach
is sound.
Below I have created a sample spreadsheet with 2 columns and 6 rows. 
In column A, I have put some words from your list.  In Column B you
will see the result of a formula that you would type.

In this example, you would type the follwing formula in cell B1, then
copy the formula and paste it into the remaining rows where you would
like the calculation to occur (in this example, cells B2 through B6).

The formula is:     =LEN(a1)&a1
When the formula is pasted into the rows of column B, the row numbers
will automatically increment, giving you the desired result.

   ----A----   ----B----
1  aba         3aba
2  abaca       5abaca
3  abacist     7abacist
4  aback       5aback
5  abacus      6abacus
6  abaft       5abaft


You can then highlight the words in columns A and B, and perform your
sort based upon Column B (Data | Sort | Column B | Ascending).  You
will now have the words in your desired order.

I hope that helps.

I quickly obtained the following results, using the answer
provided by secret901-ga in Question #208349:

aba		3
abaca		5
aback		5
abaft		5
abamp		5
abase		5
abash		5
abate		5
abacus		6
abacist		7
abalone		7
abandon		7
abampere	8
abandoned	9
abatement	9

This is what you were looking for, yes?

Just a comment on snapanswer's approach.  It works only if the
words/strings are at most 9 characters long.  Note that 11Christopher
would come before 2Al.

The two-column sort is pretty much the minimal way to do it, although
as secret901-ga originally observed, because Excel uses a "stable"
sort algorithm, one can acheive the desired result by sorting first on
the alphabetic column and second on the lengths.

regards, mathtalk-ga