Sorting question sort of

[ordnance1]

I am looking for a way to move my data up without using sort. In the example
below there is a list of employees, what I need is something that will give
the result shown in the second example after I removed Bill and Nancy. The
items can not be sorted since they have to maintain there position relative
to each other. Deleting the cells (and having the cells below move up) is
not an option. Nor is it an option to create a hidden row and assigning each
name a ranking number to sort on.

B3 = John
B4 = Bill
B5 = Linda
B6 = Adam
B7 = Nancy
B8 = Robert
B9 = Brian


B3 = John
B4 = Linda
B5 = Adam
B6 = Robert
B7 = Brian
B8 =
B9 =

 

[Rick Rothstein]

Deleting the cells (and having the cells below move up) is not an option.

Based on your example, I don't see why the above would do what you want. Is
there other data that is involved? If so, what happens to the data that was
assigned to Bill and Nancy?

 

[ordnance1]

Sorry yes there is other data. I just provided this as an example, but in
reality this is a vacation calendar and for the people that have an approved
vacation I sort them by name and that takes Care of any blank cells with in
that group. Then I have 7 Next In Line positions and these can not be sorted
because unlike the people that have approved vacation (there names can
appear in any order, we prefer alphabetical) the Next In Line positions are
first come first serve so we must preserve the order in which they were
placed on the list. Since there are other days on the calendar below them I
can not delete the cell and move those below up.

 

[Rick Rothstein]

I'm afraid I'm still not clear on what is going on here. What happens to the
data on the rows with Bill and Nancy's names on them when Bill and Nancy's
names are deleted? Do they get preserved and moved down? If preserved and
moved down, do they have to stay in their current order or can just go to
the bottom in the order they are deleted?

 

[ordnance1]

I also sent a reply in html format with a screen shot. let me know if you
were unable to view it.

 

[ordnance1]

Just picture a calendar and these are names listed for a particular day (each day is made up of a group of cells in my case each day has 47 cells aligned in a column for example B5:B47) see picture below (I hid some rows to keep the pictue small). Each color represents a different workgoud and the grey cell are the Next Line cells.