Log In
Register
Home
New Member
March 12, 2019
Offline
Hi, I need some help to read numbers/text and output it to a summary table with multiple criterion using an excel formula. It is easily done using pivot tables but my audience is not tech savvy and need a formula to extract the values from Tab1.
Tab 1: Raw data with information
Tab 2: Example of dummy data. Basically, need to read text from column C, D, E in Raw Data
Tab 1 based on 2 criterion (Pillar and Group - columns A, B in Tab 1) and then spit out results in Tab 2 - Desired output. Note that in some cases there is more than one date/value to extract out based on the criterion.
Criteria should be based on Column A and B (Group and Pillar) ....
and when the criteria meets, it may or may not have more than 1 value (for example: Group 1, Pillar 1 might have 2 dates/values in column C for example. so I have to pull both dates in my summary table.
Any help would be greatly appreciated.
Thank you!
NOTE: Ideal would be an excel formula but if its not possible, VBA help will be appreciated with easy to understand comments.
November 8, 2013
Offline
Hi Dale,
If users cannot handle a simple pivot table, then I doubt that they will be able to handle complex array formulas.
As you mentioned, pivot tables are best for this, I suggest using a macro that will create the pivot when user clicks a button, it's much more easier than formulas.
The code Assumes that the data is organized in a defined table named "Table1", as in the file attached.
Sub CreatePivot()
Dim Wks As Worksheet, PvtName As String, pt As PivotTable, pivotFld As PivotField
Set Wks = ThisWorkbook.Worksheets.Add
PvtName = "PivotTable" & Format(Now(), "yyyymmddhhmmss")
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1").CreatePivotTable TableDestination:="'" & Wks.Name & "'!R1C1", _
TableName:=PvtName
Set pt = Wks.PivotTables(PvtName)
With pt.PivotFields("Group")
.Orientation = xlRowField
.Position = 1
End With
With pt.PivotFields("Pillar")
.Orientation = xlRowField
.Position = 2
End With
With pt.PivotFields("TARGETED Narrative and Content Finalized")
.Orientation = xlRowField
.Position = 3
End With
With pt.PivotFields("TARGETED Working Data Available in Final Format")
.Orientation = xlRowField
.Position = 4
End With
With pt.PivotFields("TARGETED Final Data Refreshed")
.Orientation = xlRowField
.Position = 5
End With
pt.RowAxisLayout xlTabularRow
With pt
.ColumnGrand = False
.RowGrand = False
End With
With pt
For Each pivotFld In .PivotFields
pivotFld.Subtotals(1) = False
Next
End With
Wks.Cells(1).CurrentRegion.EntireColumn.AutoFit
End Sub
Note that each time you press the button, a new pivot cache is created and a new pivot table is added based on the new cache. I assume that they will click only once, the next time they need to update it, they can simply right click that pivot and choose Refresh.
1 Guest(s)
