Sorting rows

N

Neil Greenough

I have a .csv spreadsheet that contains the following details in the columns
A,B and C


-2.07762
53.5114
GATSO:6354@30

-2.74783
53.3244
GATSO:3746@60

-0.76298
51.98999
GATSO:5244@30

-0.24594
52.58497
GATSO:0890@30

-2.77217
55.74874
GATSO:3110@60

-0.32181
51.33441
GATSO:4834@40

-0.39686
52.57074
GATSO:5951@70



There are over 3000 rows in my spreadsheet. What I would like is to sort the
rows according to the data in the final column. So, I would like it so that
it sorts all those that end in ....@30 first, then .....@40 second etc
etc...............

Any ideas?
 
N

Neil Greenough

I have a .csv spreadsheet that contains the following details in the columns
A,B and C


-2.07762 53.5114 GATSO:6354@30
-2.74783 53.3244 GATSO:3746@60
-0.76298 51.98999 GATSO:5244@30
-0.24594 52.58497 GATSO:0890@30
-2.77217 55.74874 GATSO:3110@60
-0.32181 51.33441 GATSO:4834@40

There are over 3000 rows in my spreadsheet. What I would like is to sort the
rows according to the data in the final column. So, I would like it so that
it sorts all those that end in ....@30 first, then .....@40 second etc
etc...............

Any ideas?
 
F

FxM

Neil said:
I have a .csv spreadsheet that contains the following details in the columns
A,B and C


-2.07762 53.5114 GATSO:6354@30
-2.74783 53.3244 GATSO:3746@60
-0.76298 51.98999 GATSO:5244@30
-0.24594 52.58497 GATSO:0890@30
-2.77217 55.74874 GATSO:3110@60
-0.32181 51.33441 GATSO:4834@40

There are over 3000 rows in my spreadsheet. What I would like is to sort the
rows according to the data in the final column. So, I would like it so that
it sorts all those that end in ....@30 first, then .....@40 second etc
etc...............

Any ideas?
Click to expand...

Ni Neil,

Add a 4th column with =right(Cx,2)
Select all data and sort

@+
FxM
 
R

Ragdyer

You can use (add) a helper column next to your Column C, extract the last 2
characters (numbers), then sort all 4 columns, using the "helper" column as
the sort key.

With your data starting in A1 to C1, enter this in D1:

=RIGHT(C1,2)

Then copy down as needed.
You might try selecting D1, and then double clicking on the "fill handle"
(little black square in lower right corner of cell), which will
*automatically* copy the formula down Column D, as far as there is data in
Column C.

Now, select the *entire* datalist, including the "helper" Column D, and
sort, using Column D as the sort key.
 
R

Ron Rosenfeld

That never seemed to work unfortunately.

Any other ideas?
Click to expand...

The recommended method should work.

What version of XL do you have?

What, exactly, did you do and what were the results?

What, exactly, are your expected results.

The method recommended should work, but if you have an older version of Excel,
you may have to explicitly force the conversion in the recommended formula to
numeric. Newer versions of Excel should give you the option, when you select
to sort, of sorting the values as numbers or text.

e.g. =--RIGHT(Cx,2)


--ron
 
Top