Sorting Question

G

Guest

Dear experts,

I've a table and one of the columns is for the box numbers. The users
usually input the box number as "2006-101" which 2006 represents the year and
101 as the box equence number. When I consolidate all data together, the box
numbers are in the order below:

2005-101
2005-1018
2006-2009
2005-673
2006-399
2006-260

When I click the sort button (in ascending order), the numbers are in the
order like:

2005-101
2005-1018
2005-673
2006-2009
2006-260
2006-399

My question is, how to sort the numbers so that the year prefix will come
first then the box numbers, such as: 2005-673 will go before 2005-1018 and
2006-260 & 2006-399 will go before 2006-2009? If I split this column into two
and then sort two columns separately, it may work but it seems a bit clumsy.

Please advise the best ways.

Thanks in advance.
 
H

Harlan Grove

Freshman said:
I've a table and one of the columns is for the box numbers. The users
usually input the box number as "2006-101" which 2006 represents the year
and 101 as the box equence number. When I consolidate all data together,
the box numbers are in the order below:

2005-101
2005-1018
2006-2009
2005-673
2006-399
2006-260
Click to expand...

These are text.
When I click the sort button (in ascending order), the numbers are in the
order like:

2005-101
2005-1018
2005-673
2006-2009
2006-260
2006-399
Click to expand...

This is how text is ordered. Just like "aaaa" comes before "ab", "1018"
comes before "673".
My question is, how to sort the numbers so that the year prefix will come
first then the box numbers, such as: 2005-673 will go before 2005-1018 and
2006-260 & 2006-399 will go before 2006-2009? If I split this column into
two and then sort two columns separately, it may work but it seems a bit
clumsy.
Click to expand...

The ONLY ways you could do this require two columns. Either split these
entries in two at the dash, or use formulas in the second column to
transform this text into something that could be processed as numbers. For
instance, if your sequence numbers would never exceed 6 numerals,

=LEFT(x,4)*1000000+MID(x,FIND("-",x)+1,6)

would turn these into numbers, 2005-673 would become 2006000673. Sort both
columns plus whichever others also need to be sorted by these values by the
column of these formulas. Then you could delete, clear or hide the column of
formulas. Clumsy or not, you have no alternative.
 
G

Guest

One way ..

Assume data in A1 down
In B1: =LEFT(A1,4)+0
In C1: =RIGHT(A1,LEN(A1)-5)+0
Select B1:C1 copy down.
Then select cols A to C, click Data>Sort>Sort by Col B (ascending), Then by
Col C (ascending). Click OK.
 
G

Guest

You could use text to columns to split the data into 2 columns using the
hyphen as the delimiter. This gives you 2 columns of numbers.

Then sort the data on first column, second column.

In a third column concatenate the 2 columns and reinsert the hyphen between
the values. Example =A1&"-"&B1

Regards,

OssieMac
 
A

Arvi Laanemets

Hi

To avoid such problems in future, have the input box numbers in format like
this:
2005-00101
2005-01018
2006-02009
2005-00673
2006-00399
2006-00260

To convert all existing box numbers to new one:
Into a free cloumn, enter the formula (I assume existing numbers are in
column A, and the 1st is in A2 - so the formula you inter into row 2)
=LEFT(A2,4) & "-" & TEXT(MID(A2,6,10),"00000")
(modify the formula, when you want a different number of leading 0's)
Copy the formula down for all rows containing old box numbers.
Copy the range with formulas, and use PasteSpecial>Values to replace old
numbers with new ones.
Delete the column with formulas.

Now you can sort your data properly. And be sure new data are entered
properly too.
 
G

Guest

Hi Harlan,

Thanks for your tips. Regards.

Harlan Grove said:
These are text.


This is how text is ordered. Just like "aaaa" comes before "ab", "1018"
comes before "673".


The ONLY ways you could do this require two columns. Either split these
entries in two at the dash, or use formulas in the second column to
transform this text into something that could be processed as numbers. For
instance, if your sequence numbers would never exceed 6 numerals,

=LEFT(x,4)*1000000+MID(x,FIND("-",x)+1,6)

would turn these into numbers, 2005-673 would become 2006000673. Sort both
columns plus whichever others also need to be sorted by these values by the
column of these formulas. Then you could delete, clear or hide the column of
formulas. Clumsy or not, you have no alternative.
Click to expand...
 
G

Guest

Hi Arvi,

Thanks for your tip. Regards.

Arvi Laanemets said:
Hi

To avoid such problems in future, have the input box numbers in format like
this:
2005-00101
2005-01018
2006-02009
2005-00673
2006-00399
2006-00260

To convert all existing box numbers to new one:
Into a free cloumn, enter the formula (I assume existing numbers are in
column A, and the 1st is in A2 - so the formula you inter into row 2)
=LEFT(A2,4) & "-" & TEXT(MID(A2,6,10),"00000")
(modify the formula, when you want a different number of leading 0's)
Copy the formula down for all rows containing old box numbers.
Copy the range with formulas, and use PasteSpecial>Values to replace old
numbers with new ones.
Delete the column with formulas.

Now you can sort your data properly. And be sure new data are entered
properly too.
Click to expand...
 
G

Guest

Hi OssieMac,

Thanks for your tip. Regards.

OssieMac said:
You could use text to columns to split the data into 2 columns using the
hyphen as the delimiter. This gives you 2 columns of numbers.

Then sort the data on first column, second column.

In a third column concatenate the 2 columns and reinsert the hyphen between
the values. Example =A1&"-"&B1

Regards,

OssieMac
Click to expand...
 
R

Robert McCurdy

XL is seeing these entries as text, so..
just enter them like so > 20060101 without the hyphen and be sure you use 8 digits.

Custom format these cells with a "0000-0000" number format.

GL

Regards
Robert McCurdy

Dear experts,

I've a table and one of the columns is for the box numbers. The users
usually input the box number as "2006-101" which 2006 represents the year and
101 as the box equence number. When I consolidate all data together, the box
numbers are in the order below:

2005-101
2005-1018
2006-2009
2005-673
2006-399
2006-260

When I click the sort button (in ascending order), the numbers are in the
order like:

2005-101
2005-1018
2005-673
2006-2009
2006-260
2006-399

My question is, how to sort the numbers so that the year prefix will come
first then the box numbers, such as: 2005-673 will go before 2005-1018 and
2006-260 & 2006-399 will go before 2006-2009? If I split this column into two
and then sort two columns separately, it may work but it seems a bit clumsy.

Please advise the best ways.

Thanks in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Sorting 2
Dates not sorting correctly in Report 9
Date Sorting 2
Sorting Records with hyphen 4
Move Column Values to Rows - THANKS in Advance 3
Count issue continued from "text then date ascending count" 8
how to conditional sum in a matrix 6
Need assistance: Count non-blank within date range 5

Top