Need help with auto sorting in Excel

Question

I need Excel to auto sort smallest to largest descending and pull other cells to the new location as a new time is entered.

In detail:

Column C is time of day for a specific event (in military time) beginning on Row 5.  When a user enters a new event and assigns a time to it, I need that corresponding information to sort into the correct event row.  Below is a capture of the worksheet.

So for instance; The data entered for Event #10 beginning with Cell C14 and ending with Cell I14 would need to move to the row for Event #1 (Cells C5 to I5).  So basically I just need Columns "C" through "I" beginning on row 5 to auto sort based off of the time entered in column C so that the proper Event # is met based off of the time of day that it occurs.  I believe this requires a Macro but I am unfamiliar with those.

Answer 1

Downloaded and get same results as you did. . . . .stack overflow

I will keep trying but no promises.

I have become even more slow-thinking lately so may not be any help.

Gord

Answer 2

I need Excel to auto sort smallest to largest descending and pull other cells to the new location as a new time is entered.

In detail:

Column C is time of day for a specific event (in military time) beginning on Row 5.  When a user enters a new event and assigns a time to it, I need that corresponding information to sort into the correct event row.  Below is a capture of the worksheet.

So for instance; The data entered for Event #10 beginning with Cell C14 and ending with Cell I14 would need to move to the row for Event #1 (Cells C5 to I5).  So basically I just need Columns "C" through "I" beginning on row 5 to auto sort based off of the time entered in column C so that the proper Event # is met based off of the time of day that it occurs.  I believe this requires a Macro but I am unfamiliar with those.

Hello,

I am afraid the whole process and the corresponding spreadsheet have not been thought through from end to end.

Normally you would just start with a simple design "input, calculation, output". Later on you can think of shrinking this (condensing it to an "optimal" minimum), once you are confident this approach will work under normal as well as under error conditions.

Let us take your example: You have at least three possible times to consider if somebody needs to meet another person in order to pick up something:

1. The time you get the idea and you enter this (entry time)
2. The time Reilly has to meet Meghan in the office
3. The time the truck has to be picked up

So why should the event time be 18:30 when the (obviously most important) pick up time is 16:00 - 16:30? Maybe you need to take date and time, not just time?

Which is really the driver for the event time? Should this example have two entries (Reilly needs to meet Meghan in the office, and Reilly needs to pick up the truck)?

So far I just looked at the data. Now you can think about possible entries from different people at different times, hacking in erroneous data which gets corrected / deleted / overwritten by the same or other people, maybe once, maybe more often. You might want to define cut off times for next day's plan or you can end up with inconsistent status.

This is a fairly simple problem but maybe it's treated far to simply.

Regards,

Bernd

Answer 3

Hi,

try this approach...

data in sheets: Monday, Tuesday, ...

in columns A - I

right click on Monday tab (also in Tuesday tab,... ), select View Code and paste in the following:

[edit..]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column <> 1 Or Target.Value <> "Date" Then

MsgBox "wrong, select <Date> in column A"

Exit Sub

End If

Target.CurrentRegion.Sort Key1:=Cells(Target.Row, "C"), Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub

now,

select 'Date' in column A and press double click

in order to sort data in the specific section

(sort by column C, Time)

1st section A4:I14

2nd section A17:I27

ect.....

xxxxxxxxxxxxx

sample

1. before (double click in cell A17)

2. result

Answer 4

Hi,

try this approach...

data in sheets: Monday, Tuesday, ...

in columns A - I

right click on Monday tab (also in Tuesday tab,... ), select View Code and paste in the following:

[edit..]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Column <> 1 Or Target.Value <> "Date" Then

MsgBox "wrong, select <Date> in column A"

Exit Sub

End If

Target.CurrentRegion.Sort Key1:=Cells(Target.Row, "C"), Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub

now,

select 'Date' in column A and press double click

in order to sort data in the specific section

(sort by column C, Time)

1st section A4:I14

2nd section A17:I27

ect.....

xxxxxxxxxxxxx

sample

1. before (double click in cell A17)

2. result

This works perfectly except I need to exclude Column B from this sort, is that a possibility?

Answer 5

I need Excel to auto sort smallest to largest descending and pull other cells to the new location as a new time is entered.

In detail:

Column C is time of day for a specific event (in military time) beginning on Row 5.  When a user enters a new event and assigns a time to it, I need that corresponding information to sort into the correct event row.  Below is a capture of the worksheet.

So for instance; The data entered for Event #10 beginning with Cell C14 and ending with Cell I14 would need to move to the row for Event #1 (Cells C5 to I5).  So basically I just need Columns "C" through "I" beginning on row 5 to auto sort based off of the time entered in column C so that the proper Event # is met based off of the time of day that it occurs.  I believe this requires a Macro but I am unfamiliar with those.

Hello,

I am afraid the whole process and the corresponding spreadsheet have not been thought through from end to end.

Normally you would just start with a simple design "input, calculation, output". Later on you can think of shrinking this (condensing it to an "optimal" minimum), once you are confident this approach will work under normal as well as under error conditions.

Let us take your example: You have at least three possible times to consider if somebody needs to meet another person in order to pick up something:

1. The time you get the idea and you enter this (entry time)
2. The time Reilly has to meet Meghan in the office
3. The time the truck has to be picked up

So why should the event time be 18:30 when the (obviously most important) pick up time is 16:00 - 16:30? Maybe you need to take date and time, not just time?

Which is really the driver for the event time? Should this example have two entries (Reilly needs to meet Meghan in the office, and Reilly needs to pick up the truck)?

So far I just looked at the data. Now you can think about possible entries from different people at different times, hacking in erroneous data which gets corrected / deleted / overwritten by the same or other people, maybe once, maybe more often. You might want to define cut off times for next day's plan or you can end up with inconsistent status.

This is a fairly simple problem but maybe it's treated far to simply.

Regards,

Bernd

What you're stating is erroneous.  I appreciate the input but I don't need the workbook to be reformatted....I just need the cells to auto sort based off of the time entered.  There is no such thing as end time as each event may take anywhere from 30 minutes to 10 hours.  In those latter cases I will be closing out the other events for the day so that everyone knows the drivers are booked up.  We only have 2 drivers and only a handful of people will be using this sheet (on a shared drive in our terminal) and I don't need them confusing themselves any more than they already are.  It is simply a reference guide so that everyone knows our drivers are scheduled for specific times of the day.  They have the opportunity to enter new events but if there is a conflict, dispatch will inform them that their requested time does not work.  The other data in the Notes section are case by case and only apply to the person entering it/involved with it.