If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
IF AND OR functions
How exactly are tickets entered into August Tickets Log. Better question is, how exactly do you distincts Matthew's ticket to Marsh's tickets? Is there a column that has their names in it? How many columns total are there in the inital log? I am guessing 9... =) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
#12
|
|||
|
|||
IF AND OR functions
There is a sheet 'August Ticket Log' - A:3 Header is 'Truck Co.', B:3
Header is 'Operator', C:3 = 'Lease, D:3 = 'Date', E:3 = 'Ticket #', F:3 = 'Truck #', G:3 = 'BBLS', H:3 = 'Type', J:3 = 'Amount' (I:3 is skipped). I begin entering data on A:4. I want to insert a sheet that has control buttons: 1 - Enter tickets, 2 - Invoice Matthews Week 1, Week 2, Week 3, etc. and then one for each trucking co., then a button for supplemental by truck #, and last one for supplemental by lease. Unless you might have a better way? You are so nice for taking so much time with me.... Really I do appreciate it. I have looked for an advanced excel book, but haven't found one I like yet. Do you recommend any? I can pick one up this w/e. Thanks, Rondia |
#13
|
|||
|
|||
IF AND OR functions
I am heading to a meeting right now.. I will work on this a bit more when I come back.. but I do have some ideas I will share with you. -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
#14
|
|||
|
|||
IF AND OR functions
Thank you....
|
#15
|
|||
|
|||
IF AND OR functions
And Matthew/Marsh is the operator? right? -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
#16
|
|||
|
|||
IF AND OR functions
No Matthew, Marsh, etc are the trucking companies. There are several
trucking companies, several operators, and hundreds of leases... Bearacade wrote: And Matthew/Marsh is the operator? right? -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
#17
|
|||
|
|||
IF AND OR functions
Ok, that's what I wanted to know, we are sorting by Truck Co. I am working on a Macro where it will automatically take your top sheet, Seperate out the Truck Co, and then weeks in the month. Running into a little snag with the weeks, give me a little time =) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
#18
|
|||
|
|||
IF AND OR functions
You are very sweet. Thank you.
Bearacade wrote: Ok, that's what I wanted to know, we are sorting by Truck Co. I am working on a Macro where it will automatically take your top sheet, Seperate out the Truck Co, and then weeks in the month. Running into a little snag with the weeks, give me a little time =) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
#19
|
|||
|
|||
IF AND OR functions
Ok.. here is what I have so far. I have included a zipped excel file for you to look at. The code could probably be written tighter.. I am not the best coder around, just taking a stab at it. Basically what I have done is this: I included two macros, one is called SortByCompany and the other is call SortByWeek SortByCompany will take your master sheet as break down and filter out as many companies as you have, so if you have 15 companies, it will create 15 new sheets with their names on it and their data. SortByWeek can then be used in either those sheets or in the mastersheet. It basically takes the date and break it down into weeks, each week begins with Saturday and ends with Friday. The assumtion is that all the entries will be of the same month. Strange things will happen if they are not.. Here are the codes or you can download the sheet. You would have to come to excelforum to download it: http://www.excelforum.com/showthread...hreadid=572736 Good luck, We can continue to tweak it as you need, I hope this helps out. Sub FilterByCompany() Dim MyUniqueList As Variant, i As Long, sName As String 'stores the ActiveWorkbook.Name so we can return to it sName = ActiveSheet.Name 'Sort the Data for Filter Rows("4:2500").Select Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Key2:=Range("D4"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal MyUniqueList = UniqueItemList(Range("A4:A2500"), True) For i = 1 To UBound(MyUniqueList) 'Sort out the Data Rows("3:2500").Select Selection.AutoFilter Field:=1, Criteria1:="=" & MyUniqueList(i) Cells.Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Sheets.Add 'Create New Sheet ActiveSheet.Paste 'Paste the Data 'Get month of Data, this is the fastest way I can think of without going thru a lot of codes... Range("A1").FormulaR1C1 = "=(TEXT(MONTH(R[3]C[3])*29, ""MMMM""))" 'Rename the Sheet ActiveSheet.Name = MyUniqueList(i) & " - " & Range("A1") Range("A1") = Null 'Switch Back to orginal Sheet and deactivate autofilter Sheets(sName).Activate Selection.AutoFilter Range("A1").Select Next i End Sub Sub FilterByWeek() Dim i As Long, sName As String, mStart As Date, mEnd As Date, mTemp As Date, mTemp1 As Date 'Set up the Weeks Range("A1").FormulaR1C1 = "=(DATE(YEAR(R[3]C[3]),MONTH(R[3]C[3])+1,0))" mEnd = Range("A1").Value Range("A1").FormulaR1C1 = "=(DATE(YEAR(R[3]C[3]),MONTH(R[3]C[3]),1))" mStart = Range("A1").Value Range("A1").FormulaR1C1 = Null mTemp = NthDayOfMonth("Fri", CDate(mStart), 1) i = 1 Do While mTemp = mEnd If i = 1 Then mTemp1 = mStart Else mTemp1 = mTemp - 6 End If sName1 = ActiveSheet.Name Rows("3:25").Select Selection.AutoFilter Field:=4, Criteria1:="=" & mTemp1, Operator:=xlAnd, Criteria2:="=" & mTemp Cells.Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = sName1 & " - Week " & i Sheets(sName1).Select Selection.AutoFilter i = i + 1 mTemp = mTemp + 7 If mTemp mEnd Then sName1 = ActiveSheet.Name Rows("3:25").Select Selection.AutoFilter Field:=4, Criteria1:="=" & mTemp - 6, Operator:=xlAnd, Criteria2:="=" & mEnd Cells.Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = sName1 & " - Week " & i Sheets(sName1).Select Selection.AutoFilter End If Loop End Sub Private Function UniqueItemList(InputRange As Range, HorizontalList As Boolean) As Variant Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant Application.Volatile On Error Resume Next For Each cl In InputRange If cl.Formula "" Then cUnique.Add cl.Value, CStr(cl.Value) End If Next cl UniqueItemList = "" If cUnique.Count 0 Then ReDim uList(1 To cUnique.Count) For i = 1 To cUnique.Count uList(i) = cUnique(i) Next i UniqueItemList = uList If Not HorizontalList Then UniqueItemList = _ Application.WorksheetFunction.Transpose(UniqueItem List) End If End If On Error GoTo 0 End Function Private Function NthDayOfMonth(Which_Day As String, Which_Date As String, Occurence As Byte) As Date Dim i As Integer Dim iDay As Integer Dim iDaysInMonth As Integer Dim FullDateNew As Date Dim lCount As Long Which_Date = CDate(Which_Date) Select Case UCase(Which_Day) Case "SUN" iDay = 1 Case "MON" iDay = 2 Case "TUE" iDay = 3 Case "WED" iDay = 4 Case "THU" iDay = 5 Case "FRI" iDay = 6 Case "SAT" iDay = 7 End Select FullDateNew = DateSerial(Year(Which_Date), Month(Which_Date), 1) iDaysInMonth = Day(DateAdd("d", -1, DateSerial _ (Year(Which_Date), Month(Which_Date) + 1, 1))) For i = 0 To iDaysInMonth If Weekday(FullDateNew + i) = iDay Then lCount = lCount + 1 End If If lCount = Occurence Then NthDayOfMonth = FullDateNew + i Exit For End If Next i End Function -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
#20
|
|||
|
|||
IF AND OR functions
forgot to attach the file. Here it is: +-------------------------------------------------------------------+ |Filename: sorter1.zip | |Download: http://www.excelforum.com/attachment.php?postid=5235 | +-------------------------------------------------------------------+ -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=572736 |
Thread Tools | |
Display Modes | |
|
|