sorting

  • Thread starter Thread starter jv
  • Start date Start date
J

jv

Good day to all,

I want to sort our list of key distribution through
ascending arrangement. Unfortunately, the result is not
that accurate.
 
Could you be more specific? Are you sorting numeric values? If
so, it might be the case that some of the value are actually text
values, not numbers. Please provide more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
so sorry, i dont mean to send an incomplete info., i
accidentally pressed send.

Hereunder is my sample, it was sorted thru "Key Ref." and
it did not follow the correct sequence in numerical order,
suppose to be "1A3-2" after "1A3-1".

Date Key No.
Dept. Issue Ref. Location Issue
RD-HK 04.05.02 1A3-1 Apartment N° 101 2
RD-HK 04.05.02 1A3-10 Apartment N° 110 2
RD-HK 04.05.02 1A3-11 Apartment N° 111 2
RD-HK 04.05.02 1A3-17 Apartment N° 117 2
RD-HK 04.05.02 1A3-18 Apartment N° 118 2
RD-HK 04.05.02 1A3-19 Apartment N° 119 2
RD-HK 04.05.02 1A3-2 Apartment N° 102 2
RD-HK 04.05.02 1A3-20 Apartment N° 120 2
RD-HK 04.05.02 1A3-21 Apartment N° 122 2
RD-HK 04.05.02 1A3-22 Apartment N° 123 2
RD-HK 04.05.02 1A3-3 Apartment N° 103 2
RD-HK 04.05.02 1A3-4 Apartment N° 104 2
RD-HK 04.05.02 1A3-5 Apartment N° 105 2

Is there a simple way to arrange this, i have more 500
rows of keys to re-arrange.

For your assistance.


jv
 
It is sorting according to text rules since it is text. You would need to
put in a two dummy columns to split your values (unless all keyref start
with 1A3.

=--Right(C2,len(C2)-Find("-",C2))

If you need to split out the first part

=Left(C2,find("-",C2))
or
=Left(C2,find("-",C2)-1)

then you can use these columns with the formulas to sort.

--
Regards,
Tom Ogilvy



so sorry, i dont mean to send an incomplete info., i
accidentally pressed send.

Hereunder is my sample, it was sorted thru "Key Ref." and
it did not follow the correct sequence in numerical order,
suppose to be "1A3-2" after "1A3-1".

Date Key No.
Dept. Issue Ref. Location Issue
RD-HK 04.05.02 1A3-1 Apartment N° 101 2
RD-HK 04.05.02 1A3-10 Apartment N° 110 2
RD-HK 04.05.02 1A3-11 Apartment N° 111 2
RD-HK 04.05.02 1A3-17 Apartment N° 117 2
RD-HK 04.05.02 1A3-18 Apartment N° 118 2
RD-HK 04.05.02 1A3-19 Apartment N° 119 2
RD-HK 04.05.02 1A3-2 Apartment N° 102 2
RD-HK 04.05.02 1A3-20 Apartment N° 120 2
RD-HK 04.05.02 1A3-21 Apartment N° 122 2
RD-HK 04.05.02 1A3-22 Apartment N° 123 2
RD-HK 04.05.02 1A3-3 Apartment N° 103 2
RD-HK 04.05.02 1A3-4 Apartment N° 104 2
RD-HK 04.05.02 1A3-5 Apartment N° 105 2

Is there a simple way to arrange this, i have more 500
rows of keys to re-arrange.

For your assistance.


jv
 
It's because that key ref is just plain text and excel sorts in alphanumeric
order.

You could either change those strings so that they look like:

1A3-01 (maybe 1A3-001 if you're getting close to 999 entries)

Or just insert a couple of columns to the right of the key ref column.

Then select the keyref column and do:
Data|Text to columns.
Delimited
type - (a dash) in the other box)
and put the output in the next column--don't overwrite your original data.
(and make sure you choose General for each).

Now sort your data by the helper columns and the 2nd column will sort in numeric
sequence.
 
Dave,

I tried your tips and it works! great! it lessens my work.

Thanks and regards to all of you there.

jv
 
Back
Top