Summerising Data in Excel 2010 using VBA

[JasonEnsor]

Hi Guys,

I am trying to fathom a good way of automating a process that a collegue currently does each month. Once a month data is downloaded from our PeopleSoft database in to Excel based on sickness of staff. The data looks similar to the example below

A = Division
B = Months Absence by Div
C = Area
D = months absence by area
E = staff Number
F - Staff Name
G = Num Absences
H = %Absence
I = Years absence div
J = Years absence by area

Sample Data

A B C D E F G H I J
Acad Services 2.49 Admissions 6.62% 1 Bill 2 3.19% 1.64% 3.19%
Acad Services 2.49 Admissions 6.62% 1 Bill 2 3.19% 1.64% 3.19%
Acad Services 2.49 Research Office 6.21% 5 Tom 1 1.54% 1.64% 2.11%
Business Management 1.13 Business Operations 3.1% 7 Jack 1 1.64% 2.54% 1.54%
Business Management 1.13 Business Operations 3.1% 9 Jo 1 1.89% 2.54% 1.54%
Corporate Intelligence 2.60 Statistics 2.40% 15 Tim 1 1.04% 1.04% 2.81%

The percentages are automatically worked out via PeopleSoft, so my aim is to create a summary sheet that shows all data from the year in one place. Currently this uses Column C - N to list months Aug - July (due to the academic year)
Column A Stores each Division that individual teams belong to, Column B is used to display titles for each line of data that is populated under the column headings in C - N.

Column B example:

B C
August September.....
% Year absence by Div 3.19
% Month absence by Div 1.64
Admissions
Current Month Absence by area 6.62
Current Year Absence by area 3.19
Research office
Current Month Absence by area 6.21
Current Year Absence by area 2.11

The issue i have is that a lot of data is duplicated on the peoplesoft report, an example is that the year absence data for a div will appear alongside every area in that division, for every sickness that a staff member has it will have there total sickness for the month against every instance of their name.

I am just trying to sumerise the data in such a way that each month i can automate the moving of data from the peoplesoft report in to the summary sheet.

I have posted my first thoughts in code below. basically i thought about getting the user to import the new peoplesoft report in to the workbook with the summary sheet within it. With the Peoplesoft report selected run the CopySheet data Macro, this would create a Temp tab that would reduce some of the duplicate values then past them in to the summary sheet. However at the moment this is only going in to the last month of the summary sheet. it isn't a very flexable system at the moment.

Ideally i would like to automate moving through each months reports in a workbook and adding a column for that month and then populating data.

Dim ws As Worksheet

Public Sub CreateWorksheets(worksheetName As String)
    
    Sheets.Add.Name = worksheetName
    Sheets(worksheetName).Move after:=Sheets(Sheets.Count)
    Sheets(worksheetName).Activate

End Sub

Public Sub CopySheetData()

    Set ws = ActiveSheet
    CreateWorksheets ("Temp")
    ws.UsedRange.Copy ActiveSheet.Range("A1")
    
    Call GetDataByArea
    Call PopulateAreaData
    'Call GetDataByDivision
    'Sheets.Delete ("Temp")
End Sub

Public Sub GetDataByDivision()

    Cells.Select
    ActiveSheet.Range(Cells(1, 1), _
    Cells.SpecialCells(xlCellTypeLastCell)).RemoveDuplicates Columns:=1, Header:=xlYes
    
End Sub

Public Sub GetDataByArea()

    Cells.Select
    ActiveSheet.Range(Cells(1, 1), _
    Cells.SpecialCells(xlCellTypeLastCell)).RemoveDuplicates Columns:=3, Header:=xlYes
    
End Sub

Sub PopulateAreaData()

' Admissions
    Sheets("Summary").Range("N6").Value = Sheets("Temp").Range("D2")
    Sheets("Summary").Range("N7").Value = Sheets("Temp").Range("W2")
    
' AAU
    Sheets("Summary").Range("N10").Value = Sheets("Temp").Range("D3")
    Sheets("Summary").Range("N11").Value = Sheets("Temp").Range("W3")

' Research
    Sheets("Summary").Range("N14").Value = Sheets("Temp").Range("D4")
    Sheets("Summary").Range("N15").Value = Sheets("Temp").Range("W4")
    
' Student Admin Buxton
    Sheets("Summary").Range("N18").Value = Sheets("Temp").Range("D5")
    Sheets("Summary").Range("N19").Value = Sheets("Temp").Range("W5")
    
' Student Records and Fees
    Sheets("Summary").Range("N22").Value = Sheets("Temp").Range("D6")
    Sheets("Summary").Range("N23").Value = Sheets("Temp").Range("W6")
    
' TimeTable Unit
    Sheets("Summary").Range("N26").Value = Sheets("Temp").Range("D7")
    Sheets("Summary").Range("N27").Value = Sheets("Temp").Range("W7")
    


End Sub

Sub PopulateDivisionData()

' Academic Services
    Sheets("Summary").Range("N2").Value = Sheets("Temp").Range("V2")
    Sheets("Summary").Range("N3").Value = Sheets("Temp").Range("B2")

End Sub

Any ideas or thoughts on how best to approach this would be appreciated. I can clarify on any parts that have not been explained well here.

Many Thanks

J.

 

[SkipVought]

hi,

I'd work this on at least 3 fronts: 1) trying to get a better download from PeopleSoft and 2) working the current download into a cumulative master table & 3) developing reporting from the master table.

Summary reports are a poor source for data analysis and future reporting. You need to find as detailed data as possible without the aggregations which you can do in Excel as after processing.

I look at your code and I see EXPLICIT references in the Populate routines. Seems to me that that only works ONE TIME. What happens after you have data in Sheets("Summary").Range("N26").Value.

I would have a master STRUCTURED table with ONE ROW OF HEADINGS...
[pre]
Snapshot Date | Division | Months Absence by Div | Area | months absence by area | staff Number | Staff Name | Num Absences | %Absence | Years absence div | Years absence by area
[/pre]
where Snapshot Date is the date related to a download report.

From such a table (as close to a detail table as possible) you can generate the monthly, quarterly, yearly etc reporting as required.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}