MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sorting


Posted by al long on November 07, 2000 9:31 AM

I need to sort numbers(medical records) by the last 2 digits. Our numbers run 000.00 - 245.99.
I list the numbers and sort, but can't figure out how to sort by the last 2 numbers.

ANY help will be appreicated.


Posted by Tim Francis-Wright on November 07, 2000 9:52 AM

I'll assume that your records are in columns
A through G, with the numbers in column A.
In column H, use the following formula:-
=INT(A1)+100000*(A1-INT(A1))
(this will turn xyz.ab into ab,xyz.)
Then, sort the data set using column H as the
sort key.

HTH

Posted by Ben O. on November 07, 2000 9:53 AM

Try adding a column that contains the two rightmost characters of the column containing the medical record numbers. You can use a formula like, =RIGHT(A2,2), with A2 being the cell containing the number, and "2" being the number of characters the formula returns.

-Ben

Posted by Al Long on November 07, 2000 12:48 PM

Thanks for the fast reply, still uncertain how to enter the formula you gave me, seems it wants to multiply the numbers

Posted by Tim Francis-Wright on November 08, 2000 1:53 PM

Well, you would need to copy the formula down for each of the rows in your sample.
(You could use Ben's solution, too, which is more intuitive.)