Sorting Question - Trying to force a row of data to apper in the "wrong" sort location

This is best explained with an example that I have included in this post...

Basically what I am trying to do is perform a sort based off some start times in column F but I am using a formula in Column G (Super Sort) to perform the sort procedure Column G allows me to trick excel into forcing a row of data into the wrong spot (if you look at column F) by artificialy changing its start time. This is all well and good if I have unique start times, but if any two start times are the same it fails.

Maybe I need to try something completely different or maybe I need to keep beating on this method, but that is why I came here.

Blue Tab = Original
Green Tab = It works when start times are unique
Yellow Tab = Fails when start times are not unique

Thanks in advance

Hi caliskier,

Can't quite follow your post because the three tabs seem to be different.

However, I note that in your SuperSort column, you are adding .1 in certain circumstances.

You said that these are "Start Times" however the sample start times are just numbers, not times!

If you are in fact using times, adding 0.1 will add 2 hours and 24 minutes. This will through off your sort. YOu probably should be adding 0.001.


When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi Caliskier,

Interesting question. I think you were headed in the correct direction, but got sidetracked by thinking about numbers exclusively. See the 'Possible Solution' Sheet in the attached file where I added two columns:

a. Modified Super Sort - Similar to your Super Sort, but all the 88s are still 88, and 'Project Id' 14 which must follow project 9 are both assigned the value 91.

b. Super Sort Trailing Code - This code is text with the following meanings:
N = Normal
A thru M - Are SORTED before NORMAL.
O thru Z - Are SORTED after NORMAL.
A1 - Special Code Subset
A2 - Immdiately follows A1, etc.

It's much easier to follow if you see your sample data.

c. OPTIONAL - Super Sort Leading Code - If you want items to ABSOLUTELY be first or last.
This uses the save conventions as the 'Super Sort Trailing Code'.

When you do the sort you have to sort with the Column KEYS in the following order:
a. Super Sort Leading Code
b. Modified Super Sort
c. Super Sort Trailing Code

Lewis

Want to thank both of you guys for the input, this is actualy a test case of a huge sheet that figures out placment of projects by month and I have it already set up based on number (Start times are numbered months). It feeds off a source that must be dumpped in. What I am trying to do is keep from having to update much manually.

LJ, does your solution require that I manually change columns I, J, and K if the start timing changes? The supersort has to have a formula in it, and so I kept that file and used your a,b,c method and it still is putting the data in the wrong location.

I think I might be hitting a macro...

Originally Posted by caliskier

LJ, does your solution require that I manually change columns I, J, and K if the start timing changes?
I think I might be hitting a macro...

I missed your formulas in the 'Super Sort' column. Yes, I put my input in manually, to prove to myself that what you wanted could be done. I figured your real project is a lot more complicated than your excellent sample.

I imagine some kind of outlier lookup table could be constructed, to do what you want automatically, if you wanted to avoid Macros. If you wanted to use Macros, you could develop some rules for which line items must be married, and then do a regular sort. Macros could then make sure the 'married pairs (trios, etc.) were in their proper locations.

Lewis

Originally Posted by LJMetzger

I missed your formulas in the 'Super Sort' column. Yes, I put my input in manually, to prove to myself that what you wanted could be done. I figured your real project is a lot more complicated than your excellent sample.

I imagine some kind of outlier lookup table could be constructed, to do what you want automatically, if you wanted to avoid Macros. If you wanted to use Macros, you could develop some rules for which line items must be married, and then do a regular sort. Macros could then make sure the 'married pairs (trios, etc.) were in their proper locations.

Lewis

Wanted to thank you for your time and help, looks like I am going to spend the weekend cracking this in front of the football game.