Sorting, but not ascending/descending

[VBA]

Heya,

I've got the following spreadsheet:

As you can see, I've sorted it by column A. However, sometimes the date is the same and then it doesn't always go right... see f.ex. 17 dec, where the ship arrives in the harbour and then is at sea again and then departs Not exactly what happens. I had the same problem on 28 dec, but there it disappeared when I told Excel to sort secondly on column B.
However, this doesn't work for 17 dec.

So I need a way to sort such, that D always follows on A (after arrival, one departs) and S might be either after D or before A but not before D or after A... If you follow

Can I do this in Sort (some sort of "custom sort") or should I write a macro for it?

Thanks in advance.

 

[Andrew Poulsom]

It seems to me that you should correct your data so that the date at sea can't be before the date of departure.

 

[VBA]

I'm not quite sure what you mean.

This data is delivered to me this way, the only thing I have to do is sort it with what I know - thus using column A (date) and B. It might indeed be useful to also add a time to the date, but currently that isn't the case.

I take it this means that it isn't possible to use the option "sort" in a manner which isn't ascending/descending?

 

[Andrew Poulsom]

You can use a custom sort, but it wouldn't help in your case. If the order is A D S and you are also sorting by date, the date of D cannot be after the date of S.

 

[Bdra]

Just to second Andrew and elaborate a little more, there is an error in the data you are working with. As it has been recorded, the same ship "Arrived" in the harbor 13.10 - 1.50 and was simultaneously at "Sea" 13.24 - 1.47. There is no way a sort is going to fix that; you are working with bad data in this instance.

 

[VBA]

Ow, sorry if it's confusing

The first column A is the date
the second column B is the status, thus arrived (A), departed (D) or at sea (S)
the third and fourth column give the draft (how deep it's in the water) fore and aft of the ship - thus the loading condition

So in the beginning of one day the ship can be at sea (S) and than later arrive (A). Or depart early in the morning (D) and be at sea (S) later on the same day. Or (if within the same harbour) arrive and depart on the same day. Even (if within the same harobour) departure and arrival on the same day is possible...

I guess your answers indicate that I should write a loop like "if date is alike and D follows S, switch rows etc".

 

[Andrew Poulsom]

Ow, sorry if it's confusing

The first column A is the date
the second column B is the status, thus arrived (A), departed (D) or at sea (S)
the third and fourth column give the draft (how deep it's in the water) fore and aft of the ship - thus the loading condition

So in the beginning of one day the ship can be at sea (S) and than later arrive (A). Or depart early in the morning (D) and be at sea (S) later on the same day. Or (if within the same harbour) arrive and depart on the same day. Even (if within the same harobour) departure and arrival on the same day is possible...

I guess your answers indicate that I should write a loop like "if date is alike and D follows S, switch rows etc".

I wasn't confused and there is no need for a loop.

A ship can't be at sea before it departs. So the D on 18 Dec 2008 must be incorrect because it comes after the S on 17 Dec 2008. Correct that and your current method of sorting will work.

 

[Bdra]

OK, that was clarifying.

It sounds like the sequence that is possible within the same date is [S-A-D-S], or any sub-section thereof [S-A], [D-S], etc.

You could write a custom sort routine that (1) sorts by date, and (2) enforces this sequence within dates.

The problem is that there seems to be no way to tell the first and last S apart. So you would have to set up a set of rules -- if S and A are on the same date, S comes first. If S and D are on the same date, D comes first.

But if you have all three statuses recorded (S, A, and D) on a single date, there seems to be no way to tell whether the S belongs at the start or at the end. All you know is that it can't come between the A and the D.

 

[Bdra]

To Andrew: As I understand this the A and S on 17 Dec. are reversed. The ship was at sea, then arrived on 17 Dec., and departed again on 18 Dec. A standard sort won't pick that up; I think a custom coded sort is required.

 

[Andrew Poulsom]

To Andrew: As I understand this the A and S on 17 Dec. are reversed. The ship was at sea, then arrived on 17 Dec., and departed again on 18 Dec. A standard sort won't pick that up; I think a custom coded sort is required.

Yes, I see now. Thanks for that explanation.

The best solution would be to include the time in the date.