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.
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.
