Isolating all cells by last digit

J

John R. Youngman

Hi. I'm using Excel 2003 and have a small spreadsheet (one column) with
thousands of entries. I need to isolate or group entries based on the last
digit.

Here's what I have:

2566
1231
5876
2540
4561
2960

Here's what I would like:

2540
2960
1231
4561
5876
2566

Any ideas on how to do this?

TIA

John
 
E

Earl Kiosterud

John,

I think you'll need a helper column. Presuming your stuff is in column A,
put either of these in another column, and copy down with the fill handle:

=MOD(A1, 10)
=RIGHT(A1, 1)

Now sort the table on that column. You can hide it when you don't need it.
 
J

JWolf

Add a helper column B with the formula =RIGHT(TEXT(A1,0),1) copied down.
Sort with it as the first sort key.
 
J

JE McGimpsey

One way:

In an adjacent "helper" column enter

=MOD(A2,1)

and copy down.

Sort both columns on the helper column.

Note that your example is inconsistent in sorting with 5876 above 2566.
 
J

John R. Youngman

Thanks, Earl. Both methods worked great!

John


Earl Kiosterud said:
John,

I think you'll need a helper column. Presuming your stuff is in column A,
put either of these in another column, and copy down with the fill handle:

=MOD(A1, 10)
=RIGHT(A1, 1)

Now sort the table on that column. You can hide it when you don't need it.
Click to expand...
 
J

John R. Youngman

Thanks. Worked just right.

John


JWolf said:
Add a helper column B with the formula =RIGHT(TEXT(A1,0),1) copied down.
Sort with it as the first sort key.
Click to expand...
 
J

John R. Youngman

Thanks for the quick reply. You're right about my example being
inconsistent on the last two entries. Your answer was very similar to
another:

=MOD(A1,10) compared to your =MOD(A2,1)

I know the "A1" is a cell reference; why did you recommend "1" instead of
"10" ??

Just curious.

Thanks again.

John
 
Top