SORTING BETWEEN WORKSHEETS

[Unknown Soldier]

I HAVE THREE COLUMN ON SHEET1 NAMED AND LOOK LIKE THE BELOW:
A1 B1 C1
NAME START END
A 9 AM 5 PM
B
C 6 AM 12 PM
D 3 PM 9 PM
E 1 PM 8 PM

IN WORKSHEET2 IN THE SAME WORKBOOK, I WANT IT AUTO LOOK OR AUTO SORT
ACCORDING THE START TIME AND LEAVE OUT NAME WITHOUT START OR END TIME, LOOK
LIKE THIS:

A1 B1 C1
NAME START END
C 6 AM 12 PM
A 9 AM 5 PM
E 1 PM 8 PM
D 3 PM 9 PM

WHAT IF STATEMENTS OR FORMULAS DO I HAVE TO PUT IN THE CELLS IN SHEET2 TO
HAVE THE RESULTS FROM ABOVE? IS THE ABOVE POSSIBLE AS ALL? THANKS

 

[Pete_UK]

First of all, please do not SHOUT - it is considered rude.

Put this formula in B2 of Sheet2:

=IF(ISERROR(SMALL(Sheet1!B$2:B$6,ROW(A1))),"",SMALL(Sheet1!B$2:B
$6,ROW(A1)))

This will list your start times in sequence. Then put this in A2:

=IF(B2="","",INDEX(Sheet1!A$2:A$6,MATCH(B2,Sheet1!B$2:B$6,0)))

and this one in C2:

=IF(B2="","",INDEX(Sheet1!C$2:C$6,MATCH(B2,Sheet1!B$2:B$6,0)))

Copy the formulae down into rows 3:5.

If you have duplicate start-times then you will need a different
approach.

Hope this helps.

Pete

 

[Unknown Soldier]

Sorry, I did not know that was shouting.

Yeah, I have duplicate start-time, what do I need to do to fix that?



First of all, please do not SHOUT - it is considered rude.

Put this formula in B2 of Sheet2:

=IF(ISERROR(SMALL(Sheet1!B$2:B$6,ROW(A1))),"",SMALL(Sheet1!B$2:B
$6,ROW(A1)))

This will list your start times in sequence. Then put this in A2:

=IF(B2="","",INDEX(Sheet1!A$2:A$6,MATCH(B2,Sheet1!B$2:B$6,0)))

and this one in C2:

=IF(B2="","",INDEX(Sheet1!C$2:C$6,MATCH(B2,Sheet1!B$2:B$6,0)))

Copy the formulae down into rows 3:5.

If you have duplicate start-times then you will need a different
approach.

Hope this helps.

Pete

 

[Pete_UK]

Did you try the suggestions in the post from Billy Liddel?

Pete

 

[Unknown Soldier]

I don't really understand his Billy's formulas. Where do I put the
range(a1:a200)?

Tried, but did not seem to work. Yours work, but it does not take duplicate
time start.


Did you try the suggestions in the post from Billy Liddel?

Pete

 

[Unknown Soldier]

It does not work right, there is a bug in the if statement somewhere. I
added a duplicate in sheet1, look like this

I HAVE THREE COLUMN ON SHEET1 NAMED AND LOOK LIKE THE BELOW:
A1 B1 C1
NAME START END
A 9 AM 5 PM
B
C 6 AM 12 PM
D 3 PM 9 PM
E 1 PM 8 PM
F 3 PM 9PM

AND THE RESULTS WITH YOUR IF STATEMENTS ARE:


1 a 9:00 AM 5:00 PM
c 6:00 AM 12:00 PM
3 d 3:00 PM 9:00 PM
4 e 1:00 PM 8:00 PM
5 f 3:00 PM 9:00 PM
6


THE TWO DUPLICATE DOES DOES SORTING UP VERTICAL AND NEXT TO EACH OTHER.
MOREOVER, I HAVE COLUM HEADING, DO I NEED TO CHANGE RANGE IF I HAVE COLUMN
HEADING?

Unknown Soldier" <nomailplease.com> wrote in

 

[Rich/rerat]

Unknown Soldier,
Try the following macro after you do the following:
1. On Sheet 1 & 2 Place the following in
A1 B1 C1
Name Start End
2. On Sheet2 Highlight the Headers in Row1> Data> Select AutoFilter.
3. Add the following Macro to Workbook, and create Button on toolbar for it.

Sub SortByName()
'
' SortByName Macro
'
Sheets("Sheet1").Select
Range("A2:C101").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2:C2").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-12
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("A2")
_
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Selection.AutoFilter Field:=2, Criteria1:="<>"
Range("D1").Select
Sheets("Sheet1").Select
Range("A1").Select
End Sub

4. Add data to Sheet1. I would add the data like this:
Col_A Col_B Col_C
Name Start End
A 3/5/08 9 AM 3/5/08 5 PM
B
C 3/5/08 6 AM 3/5/08 12 PM
D 3/5/08 3 PM 3/5/08 9 PM
E 3/5/08 1 PM 3/5/08 8 PM
F 3/1/08 3 PM 3/1/08 9PM (Date changed to show Sorting
of a late data entry in Macro)

Run Macro, and Sheet2, should be as you desire.

Your Results will look like this:
Col_A Col_B Col_C
Name Start End

F 3/1/08 3:00 PM 3/1/08 9:00 PM
C 3/3/08 6:00 AM 3/3/08 12:00 PM
A 3/3/08 9:00 AM 3/3/08 5:00 PM
E 3/3/08 1:00 PM 3/3/08 8:00 PM
D 3/3/08 3:00 PM 3/3/08 9:00 PM


If you choose to add a formula to sheet2 column D say for total hours, make
sure that you first change the filter settings to show all, before adding
the formula, and dragging down it throughout the column, then you can rerun
the Macro, and see the new results.


--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


"Unknown Soldier" <nomailplease.com> wrote in message
It does not work right, there is a bug in the if statement somewhere. I
added a duplicate in sheet1, look like this

I HAVE THREE COLUMN ON SHEET1 NAMED AND LOOK LIKE THE BELOW:
A1 B1 C1
NAME START END
A 9 AM 5 PM
B
C 6 AM 12 PM
D 3 PM 9 PM
E 1 PM 8 PM
F 3 PM 9PM

AND THE RESULTS WITH YOUR IF STATEMENTS ARE:


1 a 9:00 AM 5:00 PM
c 6:00 AM 12:00 PM
3 d 3:00 PM 9:00 PM
4 e 1:00 PM 8:00 PM
5 f 3:00 PM 9:00 PM
6


THE TWO DUPLICATE DOES DOES SORTING UP VERTICAL AND NEXT TO EACH OTHER.
MOREOVER, I HAVE COLUM HEADING, DO I NEED TO CHANGE RANGE IF I HAVE COLUMN
HEADING?

Unknown Soldier" <nomailplease.com> wrote in