I have a spreadsheet that comes to me daily with about 60K rows in it. One
of the columns has cells with 15 digit numbers in it formatted as text. I
need to do a sort by the last two numbers in that cell, and then subtotal
the number for each sorted group.

For instance, in the group below, I need to sort so the numbers ending in 01
are together and then subtotaled as 2. Then the 12 are sorted together and
subtotaled as 1.

234568975612401
123456789101112
674368465782701

I have never seen a number in this column begining with a 0, so not sure why
it is text, but I could easily cahnge to a big integer if it is easier to
sort or subtotal that way.

Thanks for any help.

You need to have an extra column
Enter the formula
=RIGHT(A1,2)
Copy it down
Copy the whole Col
Paste Special as Values

then use it to sor/subtotal etc.

"Striker" wrote:

I have a spreadsheet that comes to me daily with about 60K rows in it. One
of the columns has cells with 15 digit numbers in it formatted as text. I
need to do a sort by the last two numbers in that cell, and then subtotal
the number for each sorted group.

For instance, in the group below, I need to sort so the numbers ending in 01
are together and then subtotaled as 2. Then the 12 are sorted together and
subtotaled as 1.

234568975612401
123456789101112
674368465782701

I have never seen a number in this column begining with a 0, so not sure why
it is text, but I could easily cahnge to a big integer if it is easier to
sort or subtotal that way.

Thanks for any help.

Hi,

If you have 60,000 rows you may find the subtotal command a little slow, so
after you have created the formula Sheeloo gave you to extract the last two
digits, you might consider a pivot table. In general pivot table are very
fast, and they don't require you to sort your data before making the pivot
table because they automatically sort on Row Label and Column Label fields.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Striker" wrote:

I have a spreadsheet that comes to me daily with about 60K rows in it. One
of the columns has cells with 15 digit numbers in it formatted as text. I
need to do a sort by the last two numbers in that cell, and then subtotal
the number for each sorted group.

For instance, in the group below, I need to sort so the numbers ending in 01
are together and then subtotaled as 2. Then the 12 are sorted together and
subtotaled as 1.

234568975612401
123456789101112
674368465782701

I have never seen a number in this column begining with a 0, so not sure why
it is text, but I could easily cahnge to a big integer if it is easier to
sort or subtotal that way.

Thanks for any help.