If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
EXCEL 07 Sort
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. |
#2
|
|||
|
|||
EXCEL 07 Sort
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. |
#3
|
|||
|
|||
EXCEL 07 Sort
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. |
Thread Tools | |
Display Modes | |
|
|