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
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
Yeah, I have duplicate start-time, what do I need to do to fix that?
"Pete_UK" <pash...@auditel.net> wrote in message
news:d3a9d1ec-92eb-44d0...@47g2000hsb.googlegroups.com...
Pete
On Mar 4, 4:08 pm, "Unknown Soldier" <nomailplease.com> wrote:
> Sorry, I did not know that was shouting.
>
> Yeah, I have duplicate start-time, what do I need to do to fix that?
>
> "Pete_UK" <pashu...@auditel.net> wrote in message
> > HAVE THE RESULTS FROM ABOVE? IS THE ABOVE POSSIBLE AS ALL? THANKS- Hide quoted text -
>
> - Show quoted text -
Tried, but did not seem to work. Yours work, but it does not take duplicate
time start.
"Pete_UK" <pash...@auditel.net> wrote in message
news:a7acd497-e36d-4666...@d4g2000prg.googlegroups.com...
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
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
news:47ced670$0$16650$4c36...@roadrunner.com...