 # Simple Sort question

• How would i go about sorting numbers. What forumula what i use.
Basically, how would i sort the numbers 1 through 10
1
3
2
6
4
7
5
8
10
9

and make it go numerically 1 - 10? TIA!

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

Code
``````Range("A1:A10").Select
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom``````

If I've been helpful, let me know. If I haven't, let me know that too.

• I have a list of names, next to the names are numbers that range from 1-50 and in different order. These numbers come from another sheet in the workbook that are entered by the user. i want the names to be sorted by their number beginning with one as they are entered by the user.
how can i do that? what formula should i use?

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

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

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

• When i use that formula, all i get is the first person's name in every cell i copied the formula to and in the second column i get a 0 in ever cell

• Hey thanx for your help!
i figured out how to manipulate the formula as to how to make it work! i was wondering though if you could explain the function you gave me? so i can learn how to do it next time
thanx!

• Glad to hear you've sorted it out!

• Hi,
edited:
If value in cell A is the combination of numbers and names like

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 Domenic

=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

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

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.

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

• 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

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!