 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008SORTING BETWEEN WORKSHEETS
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 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 > I HAVE THREE COLUMN ON SHEET1 NAMED AND LOOK LIKE THE BELOW: > A1 B1 C1 [quoted text clipped - 18 lines] > 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 The formula will take duplicates just alter the ranges. for example A1: =IF(NOT(ISERROR(MATCH(Sheet1!$B1,Sheet1!$B:$B))),ROW(),"") B1: =IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROW()))) C1: =IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!B:B,SMALL($A:$A,ROW()))) D1: =IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!C:C,SMALL($A:$A,ROW()))) You might like to reduce the range say A1:A200 is this will use less processing power than looking at a whole column. Regards Peter > First of all, please do not SHOUT - it is considered rude. > [quoted text clipped - 42 lines] > > 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 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 On Mar 4, 4:49 am, "Unknown Soldier" <nomailplease.com> wrote: > I HAVE THREE COLUMN ON SHEET1 NAMED AND LOOK LIKE THE BELOW: > A1 B1 C1 [quoted text clipped - 19 lines] > 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 Did you try the suggestions in the post from Billy Liddel? Pete > Sorry, I did not know that was shouting. > [quoted text clipped - 51 lines] > > - Show quoted text - 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 On Mar 4, 4:08 pm, "Unknown Soldier" <nomailplease.com> wrote: > Sorry, I did not know that was shouting. > [quoted text clipped - 54 lines] > > - Show quoted text - Sorry for the delay - I didn't get on the comuter again yesterday. If you are still waiting I meant A1: =IF(NOT(ISERROR(MATCH(Sheet1!$B1,Sheet1!$B$1:$B$200))),ROW(),"") B1: =IF(ROW()>COUNT($A$1:$A$200),"",INDEX(Sheet1!$A$1:$A$200,SMALL($A$1:$A$200,ROW()))) C1: =IF(ROW()>COUNT($A$1:$A$200),"",INDEX(Sheet1!$B$1:$B$200,SMALL($A$1:$A$200,ROW()))) D1: =IF(ROW()>COUNT($A$1:$A$200),"",INDEX(Sheet1!$C$1:$C$200,SMALL($A$1:$A$200,ROW()))) Hope this helps Peter > I don't really understand his Billy's formulas. Where do I put the > range(a1:a200)? [quoted text clipped - 65 lines] > > > > - Show quoted text - 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 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.  SignatureAdd MS to your News Reader: news://msnews.microsoft.com Rich/rerat (RRR News) <message rule> <<Previous Text Snipped to Save Bandwidth When Appropriate>> 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 And no, the headings make no difference, for formalas go in row 1 and are copied down Regards Peter > It does not work right, there is a bug in the if statement somewhere. I > added a duplicate in sheet1, look like this [quoted text clipped - 23 lines] > > Unknown Soldier" <nomailplease.com> wrote in Mmm - a post seems to have gone missing. Yes you are right the formulas just add new entries at the bottom of the truncated list. However, if you sort the list on Start on sheet1 this will be reflected in the truncated list. Is this OK or is Rich/Reat's post suitable? Regards Peter > And no, the headings make no difference, for formalas go in row 1 and are > copied down [quoted text clipped - 29 lines] > > > > Unknown Soldier" <nomailplease.com> wrote in If you want a macro, this will do the job. The range is not hard coded and so will work regardless of how many rows there are. Sub sortStaffIn() Dim addr As String, lastRow As Long, myCol As Range Dim CopyRng As Range, dest Application.Goto Sheets("sheet1").Range("A1") Set myCol = Range("b1:b200") addr = Range("A1").CurrentRegion.Address lastRow = Application.CountA(myCol) 'sort sheet1 for copying Range(addr).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlGuess, MatchCase:=False, Orientation:=xlTopToBottom 'copy range not to include blank hours Set CopyRng = Range("A1", Cells(lastRow, 3)) 'clear sheet5 before copying data Application.Goto Sheets("Sheet5").Range("A1") ActiveCell.CurrentRegion.ClearContents CopyRng.Copy Destination:=ActiveCell 'resort sheet1 Application.Goto Sheets("Sheet1").Range("A1") Range(addr).Sort Key1:=Range("a2"), Order1:=xlAscending, Header:= _ xlGuess, MatchCase:=False, Orientation:=xlTopToBottom End Sub Regards Peter > Mmm - a post seems to have gone missing. Yes you are right the formulas just > add new entries at the bottom of the truncated list. [quoted text clipped - 38 lines] > > > > > > Unknown Soldier" <nomailplease.com> wrote in
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.