Simple Sort question

Are you just trying to sort the rows, or are you wanting to end up with all the values in one cell? If you are just wanting to sort the rows, take a look at "Sort a list" in Excel help. If your rows don't sort correctly, the cells probably contain text, not numbers--even though they might LOOK like numbers.

Using the macro recorder...

Range("A1:A10").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Assuming that your list of names are in Column A, and your numbers in Column B...

1) Select the data you want sorted in Columns A and B
2) Data > Sort > Sort by > Column B
3) Click Ok

If it doesn't sort properly, then as fifijazz has already mentioned, the numbers may be formatted as text. If so, post back.

Quote from wakkoguy

Is there a formula i could make that sort it automatically though?

Assuming your list of names and numbers are in Columns A and B, respectively, try the following:

C1, copied over to the next column and down:

=INDEX(A:A,MATCH(SMALL($B$1:$B$50,ROW(1:1)),$B$1:$B$50,0))

Glad to hear you've sorted it out!

Hi,
edited:
If value in cell A is the combination of numbers and names like
1Bob, 2Charlie, 3Adam, 4George....

in cell b2
=LEFT(A1,COUNT(--LEFT(A1,ROW($1:$16))))
holding down Ctrl+Shift keys and press Enter to get out of the formula bar
then drag down
The formula should give you the numbers without names.

hope it helps

jindon

Quote from wakkoguy

=INDEX(A:A,MATCH(SMALL(C:C,ROW(1:1)),C:C,0))

For some reason, row B wouldn't work, in Row C i have it as single digits
Row B is with 3 decimal places. like 12.345
and C is 12
could that have been the problem

The column references for the Match and Small functions should be absolute...

If you're still not able to get the formula to work, post back and provide more details -- how many columns, sample data, etc. -- since I'm not sure what you're working with.

Quote from wakkoguy

The formula works after i modified it. but why should match and small functions be absolute?

Only if you copy the formula to other cells and want the references to those columns to remain constant.

Previously you had asked how the formula works. I didn't have a chance earlier, but I do now. So here's how it works, more or less...

=INDEX(A:A,MATCH(SMALL($B$1:$B$50,ROW(1:1)),$B$1:$B$50,0))

The Small function looks in the range of cells (in this case B1 to B50) to find the lowest value. Then the Match function tells us the position of that value within the array specified (for example, the 4th cell in the range B1 to B50). Then the result returned from the Match function (the result being 4) is used to refer to a cell in the range of cells specified by the Index function (for example, the 4th row in Column A) and return the value within that cell.

Check out the help menus for a more detailed explanation of each of these functions.

Hi Domenic
Just browsed through here and found your really useful formula to sort a list into rank order. Brilliant! Thanks
KiwiSteve