Complex column sorting problem

[Jim M]

Hi all,

I'm pulling my hair out trying to get a long list of rows (1400+)
sorted by two columns of times. The problem seems to be that some
cells are empty or have a square bracket [ or ] in them - there is
nothing I can do about this as I'm working with data from another app.

Here is an example of the data un-ordered (I hope this displays
okay...);
[ 08:52
[ 06:56
00:15 ]
00/18
01:26 01:39
[ 00:40
04:50½ 05:25

And this is how I want it;
00:15 ]
00/18
[ 00:40
01:26 01:39
04:50½ 05:25
[ 06:56
[ 08:52

Simply by order of time with the emphasis on the first column, but as
you can see, some times have a forward slash and some are on a half-
minute - this is all required info.

No matter what combination of "Sort" commands I try, I always end up
with a bunch of times separated at the top or bottom. I could cut and
paste them individually - but there's about 400 of them!!

Excel is such a powerful program - there must be a way of doing this.
Macros maybe? Help!!

Cheers,
Jim

 

[Pete_UK]

What terrible data !!

You need to set up another column with proper times in, and then you
can sort using that column as the key field. Assuming your data starts
in A1, you could put this formula in B1:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=2,--
(LEFT(A1,5)&IF(ISNUMBER(SEARCH("½",A1)),":30","")),--
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),"
",""),"[",""),"]",""),"/",":"))

Format the cell as hh:mm:ss, and then copy the formula down.

Now you can sort columns A and B using B as the sort field in
ascending order to get the data in the order that you want. You can
delete column B when you've finished.

Hope this helps.

Pete

 

[Jim Cone]

http://www.contextures.com/excel-sort-addin.html

--
Jim Cone
Portland, Oregon USA

..
..
..

"Jim M" <[email protected]>
wrote in message
Hi all,

I'm pulling my hair out trying to get a long list of rows (1400+)
sorted by two columns of times. The problem seems to be that some
cells are empty or have a square bracket [ or ] in them - there is
nothing I can do about this as I'm working with data from another app.

Here is an example of the data un-ordered (I hope this displays
okay...);
[ 08:52
[ 06:56
00:15 ]
00/18
01:26 01:39
[ 00:40
04:50½ 05:25

And this is how I want it;
00:15 ]
00/18
[ 00:40
01:26 01:39
04:50½ 05:25
[ 06:56
[ 08:52

Simply by order of time with the emphasis on the first column, but as
you can see, some times have a forward slash and some are on a half-
minute - this is all required info.
No matter what combination of "Sort" commands I try, I always end up
with a bunch of times separated at the top or bottom. I could cut and
paste them individually - but there's about 400 of them!!
Excel is such a powerful program - there must be a way of doing this.
Macros maybe? Help!!
Cheers,
Jim

 

[Jim M]

What terrible data !!

Tell me about it! But thanks I've tried that and it works.

Cheers,
Jim

 

[Pete_UK]

You're welcome, Jim - thanks for feeding back.

Pete