If OB AHT = "" or 0, the value of Full AHT is IB AHT.Quote:
Originally Posted by p45cal
If IB Tasks = "" or 0, the value of Full AHT is blank
If OB AHT = "" or 0, the value of Full AHT is IB AHT.Quote:
Originally Posted by p45cal
If IB Tasks = "" or 0, the value of Full AHT is blank
Quote:
Originally Posted by Paul_Hossler
Yes I thought so, too. I'm just not sure how to go about transferring the data from the Pivot Table to a table most especially if there are missing hours in between. Like for example I ran 9AM and was not able to run 2 consecutive hours then I ran 12PM, PT will look like this:
Hourly Table IB Tasks ........
9AM
12PM
But then the table will look like this:
Hourly Table IB Tasks ........
9AM
10AM
11AM
12PM
Do I create a Pivot first then the loop to create sheets and tables?
Give me some time… I'm not full-time on this.
What I've done in the past was to use VBA to create a temporary PT on a temporary WS to do all the heavy lifting and then by selecting the appropriate page fields to 'filter' the data I could create the final reportsQuote:
Originally Posted by jazz2409
Along the way, I created any needed 'derived' fields for parameters that were not in the source data
Delete the temporary WS and I was done
That's just one way
Sometimes I created a temporary WS with a copy of the source data and any auxiliary values to make the PT easier to use
While trying to implement the above (Full AHT2 in the pic) I got this as opposed to the original Full AHT in the pic.Quote:
Originally Posted by jazz2409
Hopefully Full AHT2 is correct; can you confirm?
Attachment 25796
edit post posting:
Now I'm getting the feeling you meant to say:
If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
?
1. While creating the 'Overall' table at the top of each Lob sheet I note that the Start time (SOO column on the Database sheet) is the same for each LOB - will that ALWAYS be the case? (I'm trying to avoid looking for the latest time in a given LOB.)
2. The remaining tables are those listed in the Sub LOB for each LOB on the Database sheet; will there ever be additional Sub LOBs in the Consolidated sheet that aren't listed in the Database sheet?
3. While thinking about that, will there ever be LOBs on the Consolidated sheet that aren't in the Database sheet?
(Hopefully, you'll tell me that the Database sheet is created from the data on the Consolidated sheet and that ALL the data on the Consolidated sheet is used in creating the data in the Database sheet)
First of all I would like to apologize, I didn't mean to make you feel like I'm hurrying you.. I was just trying to analyze as well :(Quote:
Originally Posted by p45cal
That formula is correct.Quote:
Originally Posted by p45cal
1. No, the overall table's SOO is always the SOO of the first sub LOB indicated per main LOB. Or maybe we can just add another Sub LOB saying Overall so it will be easier as we will still use the code that you wrote above. Anyway I will also put a column for headcount for each LOB so I think I'll just put another Sub LOB saying OverallQuote:
Originally Posted by p45cal
2. Yes
3. No
Do you have a sample workbook of this that I can study?Quote:
Originally Posted by Paul_Hossler
I'm not sure which formula…Quote:
Originally Posted by jazz2409
Full AHT2 (15669 in cell M5 of the picture) is correct?
I'm aiming to produce two solutions, one with pivot tables and one with just formulae. This will allow comparison and if they're the same all well and good, you'll have a choice. If they're different then some detective work will be needed.
I meant this formulaQuote:
Originally Posted by p45cal
If OB AHT = "" or 0, the value of Full AHT is IB AHT/IB Tasks
Otherwise, the formula is IB AHT + (OB AHT *(OB TASKS/IB TASKS))
I noticed a difference between some pivot table values and plain formula values and drilled it down to the Consolidated sheet cells C377:C458. These cells are different in that they contain date information as well as time information, while the other cells contain only time information.
In the working model which will it be?
Full AHT is the one that's correct.Quote:
Originally Posted by p45cal
My column A is Date and Time concatenated. I can remove that I think there's no actual use for that.Quote:
Originally Posted by p45cal
My column B has to be just Date.
And my column C has to be just Time.
Maybe I accidentally changed something.
Attached
You are amazing works like a charm :bow::bow:Quote:
Originally Posted by p45cal
How does this site work, to acknowledge your help?
Also, is there a website that you can recommend me to learn VBA? I want to be like you :bow:
I need to add a few more tables that have different headers but I will study your code first then I will post back if I can't get something to work :)
How do I add a table at the end of the very last table? This table has different headers than the ones for Sub LOB. I tried to do the same as the Overall Table but it's either:
1) being added after every table per sheet
2) being added just after the last table of the very last sheet
Also, is there a way to merge two cells of a table?
Sorry I already got this working except the merging of cellsQuote:
Originally Posted by jazz2409
Quote:
Originally Posted by jazz2409
New code in blue.Code:End With
If cll.Offset(1).Value <> CurrentCat Then
NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2).Select
MsgBox "Code for new table at selected cell here"
End If
Next Cll
Merging: Yes; which cells, and why?
I need to add another table at the end of the last sub category's table.
The last snippet I gave adds a table after the last sub category on every category (LOB) sheet.
To get one table after the last sub category on only the last category sheet, put your code just before End Sub, say in the vicinity of:
Code:NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)
Yes, I got it working already however there are columns on the last table that I need mergedQuote:
Originally Posted by p45cal
Your file has an extra sheet new table, but no indication of what you want merged, nor any new code. It stumps me that you create a new table AND then want to merge columns, why not create a single column to hold the information you want in the first place?Quote:
Originally Posted by jazz2409
Sorry I forgot to indicate on that sheet what needs to be merged *facepalm*Quote:
Originally Posted by p45cal
I need to merge cell B1 and C1, D1 and E1, and F1 and G1
Merged cells and vba are notoriuosly difficult to work with in VBA although your requirement here is quite simple and probably trouble-free it's infinitely preferable to use Centre-across-Selection, achievable by selecting the 2 cells, going into Format cells…, Alignment tab and choosing Centre Across Selection in the Horizontal: field.
In code that translates to the likes of:although in the context of creating a new table in code, these may not be the actual cells involved.Code:Range("B1:C1").HorizontalAlignment = xlCenterAcrossSelection
Range("D1:E1").HorizontalAlignment = xlCenterAcrossSelection
Range("F1:G1").HorizontalAlignment = xlCenterAcrossSelection
I've been locked out of my NT login at work and couldn't login to my work laptop :omg2: I will try this as soon as the IT department has fixed it :omg2:
Okay so I was able to put the formula for the columns for the last table I was trying to put but I can't make it look like the table on the file I previously attached.
Well, I have to throw it back over to you; how did you make that table look like it looks?!Quote:
Originally Posted by jazz2409
Perhaps record a macro of you making the table look as you want it? Otherwise describe the steps you took here.
Here's the code I added:
The headers on TableHeaders2 aren't really supposed to be like that, I just don't know how to make the table look like the one on the Excel file I previously posted. The code above works however the table isn't supposed to look like thatCode:TableHeaders1 = Array(".", "IB AHT", ".", "OB AHT", ".", "Full AHT", ".")
TableHeaders2 = Array("Hourly Table", "IB > 90 days", "IB < 90 days", "OB > 90 days", "OB < 90 days", "FAHT > 90 days", "FAHT < 90 days")
If Cll.Offset(1).Value <> CurrentCat Then NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2).Select
'Create the Tenure Table here:
Set Destn = NewSht.Cells(Rows.Count, "B").End(xlUp).Offset(2)
'optionally convert table to plain range (next line only):
If Not LastTable Is Nothing Then LastTable.Unlist
With Destn
.Value = CurrentCat & " Tenure-Wise Summary"
With .Font
.Name = "Calibri"
.Size = 11
.Underline = xlUnderlineStyleSingle
.Bold = True
End With
End With
Set Destn = Destn.Offset(2)
'Destn.Resize(, 7).Value = TableHeaders1
'Set Destn = Destn.Offset(1)
Destn.Resize(, 7).Value = TableHeaders2
Set Destn = Destn.Offset(1)
StartTime = Cll.Offset(, 3).Value
If TypeName(StartTime) = "String" Then StartTime = TimeValue(StartTime)
For hr = StartTime To EndTime + 0.0001 Step 1 / 24
Destn.Value = hr
Destn.NumberFormat = "hh:mm AM/PM"
Set Destn = Destn.Offset(1)
Next hr
Set LastTable = NewSht.ListObjects.Add(xlSrcRange, Destn.Offset(-1).CurrentRegion, , xlYes)
With LastTable
.TableStyle = "TableStyleMedium14"
.ShowTableStyleRowStripes = False
'add TENURE-WISE formulae here.
On Error Resume Next
.ListColumns("IB > 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C12,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("IB < 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C12,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("OB > 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C17,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("OB < 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(SUMIFS(Consolidated!C17,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """),0)"
.ListColumns("FAHT > 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(IF([@[OB > 90 days]]="""",[@[IB > 90 days]],[@[IB > 90 days]]+[@[OB > 90 days]]*SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure > 90 days"",Consolidated!C4,""" & CurrentCat & """)),0)"
.ListColumns("FAHT < 90 days").DataBodyRange.FormulaR1C1 = "=IFERROR(IF([@[OB < 90 days]]="""",[@[IB < 90 days]],[@[IB < 90 days]]+[@[OB < 90 days]]*SUMIFS(Consolidated!C16,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)/SUMIFS(Consolidated!C11,Consolidated!C3,[@[Hourly Table]],Consolidated!C10,""Tenure < 90 days"",Consolidated!C4,""" & CurrentCat & """)),0)"
.ListColumns("Hourly Table").DataBodyRange.NumberFormat = "hh:mm AM/PM"
Range(.ListColumns(2).DataBodyRange, .ListColumns(7).DataBodyRange).NumberFormat = "0;-0;;@"
'convert to plain values:
'.DataBodyRange.Value = .DataBodyRange.Value
End With
End If
I'm in a rush, just going out...
the code you've given seems to show what doesn't work; what I'm asking is how you made that table look like it does: what formatting did you apply to make it like that? Record a new macro of you creating that table and how it looks, and post that entirely new code here. Sorry, gotta go. (A few hours)
I created the table from scratch manually :(Quote:
Originally Posted by p45cal
Yes! Do it again while recording a macro and post the code here.Quote:
Originally Posted by jazz2409
Here's the code generated by the recorder. Please note that table headers and time under hourly table are pre-typed
Code:Sub Macro3()'
' Macro3 Macro
'
'
Range("I1:O2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I3:O13").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ThemeColor = 1
.TintAndShade = 0
.Weight = xlThin
End With
Columns("I:O").Select
Range("I2").Activate
Columns("I:O").EntireColumn.AutoFit
Columns("I:O").EntireColumn.AutoFit
ActiveWindow.ScrollRow = 1
Range("J1:K1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("L1:M1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("N1:O1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
End Sub
That code boils down to:Can you work with that?Code:Sub Macro3b()
With Range("I1:O2")
.Interior.Color = 2315831
.Font.Color = 16777215
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With Range(Range("I3:O13"), Range("I3:O13").End(xlDown))
.Interior.Color = 11854022
With Intersect(.Cells, .Cells.Offset(, 1))
.HorizontalAlignment = xlCenter 'you didn't have this but you may want it
.VerticalAlignment = xlCenter 'you didn't have this but you may want it
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Color = 16777215
End With
Range("J1:K1").HorizontalAlignment = xlCenterAcrossSelection
Range("L1:M1").HorizontalAlignment = xlCenterAcrossSelection
Range("N1:O1").HorizontalAlignment = xlCenterAcrossSelection
.EntireColumn.AutoFit
End With
End Sub
Hmmm I am honestly not sure how to change the current code into that
OK, let's have the current version of the workbook…
Here's my most recent workbook
by the way I have another question. I am working on two sites: let's say Site A and Site B. Site A does not require the overall table, but Site B does. How do I do that?
Attached.