Taking 12 sheets making one summary

C

chunkylover57

I have 12 spreadsheets, one for each month that has revenue an
employee. Each sheet/month has 350 or so people and the rev, however
Not all the months have the same people and rev. How do I search the 1
sheets for the names and add all the rev? Basiclly I want to make
summary sheet...

Good things is all names are unique...

on all sheets the names start on a1 and the rev starts on b1.

Thanks..
 
J

Jim Cone

Paste the data from the 12 sheets onto a new sheet.
Do it so all data is stacked in the same columns.
Sort by name.
Use Data | Subtotal to sum rev for each employee.

Jim Cone
San Francisco, CA
 
K

Ken Wright

You can use the following to summarise the data, and then just throw it into a
Pivot Table. Put the code in a module and run it, and it will create summary
sheet with all your data on it.

Assumes your data is in Cols A&B on each sheet, and currently assumes that you
have a header row on each sheet, with for example "NAME" in A1 and "REVENUE" in
B1. If you don't have a header row on each of the sheets, and it is just the
raw data, then change the line that says StRow = 2 to StRow = 1.

------------------------------------------------

Sub SummaryData()

Dim wks As Worksheet
Dim sd As Worksheet
Dim sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

StRow = 2

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set wks = Worksheets.Add

With wks
.Move Before:=Sheets(1)
.Name = "Summary Sheet"
.Range("A1:C1").Value = Array("Sheet", "Name", "Revenue")
End With


For sht = 2 To ActiveWorkbook.Sheets.Count
Set sd = Sheets(sht)
lrow1 = wks.Cells(Rows.Count, "B").End(xlUp).Row
lrow2 = sd.Cells(Rows.Count, "B").End(xlUp).Row
sd.Activate
sd.Range(Cells(StRow, 1), Cells(lrow2, 2)).Copy wks.Cells(lrow1 + 1, 2)
wks.Cells(lrow1 + 1, 1).Resize(lrow2 - (StRow - 1), 1).Value = sd.Name
Next sht

wks.Activate

End Sub

-----------------------------------------------------

For a quick and easy intro to Pivot tables, see here:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
 
C

chunkylover57

My summary sheet is as follows

Emp Name, Jan, Feb, March, .... (each is a separte column)

I have to do this for several depts, so I am trying to avoid the Cut
and paste methods...Also this will be handed out...

Can I use the Vlookup and or match?

Thanks for the help

Although each name is unique, there are several smiths...

ie smithR
smithRi
smithA

etc...
 
D

Dave Peterson

Once you have all the names in your summary sheet (use Ken's code to build it if
you need to), you can use a bunch (a big bunch) of =vlookup()'s.

In general, your =vlookup() will look like this (assuming the names of the
"detail" sheets are Jan-Dec).

=VLOOKUP($A2,'Jan'!$A:$B,2,FALSE)
through
=VLOOKUP($A2,'Dec'!$A:$B,2,FALSE)

(the single quotes aren't required, but will be if your worksheet name is
special--has spaces, consists of only digits....)

But if the value isn't found on a detail sheet (Jan-Dec), then you'll get #n/a's
back.

You can hide them by adjusting your formula:
=IF(ISERROR(VLOOKUP($A2,'3'!$A:$B,2,FALSE)),0,VLOOKUP($A2,'3'!$A:$B,2,FALSE))


=======

But you may want to use Ken's program to create the summary.

If you sheet names are nice abbreviation of months, you can use that and build a
nice pivottable that does the work for you.

I modified Ken's code slightly to actually put a date in column A of the
intermediate summary worksheet. This makes the pivottable easier to sort (April
comes before January in an alphabetic sort).

Option Explicit

Sub SummaryData()

Dim wks As Worksheet
Dim sd As Worksheet
Dim sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long
Dim testStr As String

StRow = 2

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set wks = Worksheets.Add

With wks
.Move Before:=Sheets(1)
.Name = "Summary Sheet"
.Range("A1:C1").Value = Array("Sheet", "Name", "Revenue")
End With


For sht = 2 To ActiveWorkbook.Sheets.Count
Set sd = Sheets(sht)
lrow1 = wks.Cells(Rows.Count, "B").End(xlUp).Row
lrow2 = sd.Cells(Rows.Count, "B").End(xlUp).Row
sd.Activate
sd.Range(Cells(StRow, 1), Cells(lrow2, 2)).Copy wks.Cells(lrow1 + 1, 2)

testStr = sd.Name & " 1, 2004"
If IsDate(CDate(testStr)) Then
testStr = CDate(testStr)
Else
testStr = "'" & sd.Name
End If

wks.Cells(lrow1 + 1, 1).Resize(lrow2 - (StRow - 1), 1).Value = testStr
Next sht

wks.Columns(1).NumberFormat = "mmm"

wks.Activate

End Sub

========
After you do this, try building the pivottable manually.

Select your range A1:C9999 (or as far as you need).
Data|Pivottable
Follow the wizard until you get to a step with a Layout button
click that button
Drag the Sheet button to the column field
drag the Name button to the row field
drag the revenue button to the data field
(if it doesn't say Sum of revenue, double click on it and change it to sum.)

The finish it up.

If you like this idea, record a macro when you do it manually and add it to
Ken's code.

If you need help tweaking the code, post back with your problem (in plain
text--no workbooks) and I'm sure you'll get help.
 
P

Peo Sjoblom

One way

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"A
ug";"Sep";"Oct";"Nov";"Dec"}&"'!A2:A350"),"smithA",INDIRECT("'"&{"Jan";"Feb"
;"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!B2:B350")))

where it would check for smithA in A and sum it up in B. Of course this
means that you have to have some sort of system
in the layout so that all names are in the same range address..
Also note that for better usability replace "smithA" with a cell reference
on the summary sheet where you put your condition. e.g.

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"A
ug";"Sep";"Oct";"Nov";"Dec"}&"'!A2:A350"),B2,INDIRECT("'"&{"Jan";"Feb";"Mar"
;"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!B2:B350")))

where B2 would hold the name
 
C

chunkylover57

Thanks, code works great...

Couple of quick questions...

1. The 12 spreadsheets come in as jan.xls, feb.xls, etc, is there a
easy to code or merge all 12 into new new doc that has the orginal 12
xls into 1 master with 12 sheets?

2. Is there a way for the above code to ignore sheet "pivot"
 
D

Dave Peterson

Something like this might work for you.

I prepended the path (myPath) to each filename. I also added ".xls" (saves a
little typing).

Option Explicit
Sub combineAll()

Dim myWorkbookNames As Variant
Dim myPath As String
Dim iCtr As Long
Dim AllWkbk As Workbook
Dim tempWkbk As Workbook
Dim tempWkbkName As String
Dim wks As Worksheet

myWorkbookNames = Array("jan", "feb", "mar", "apr", "may", "jun", _
"jul", "aug", "sep", "oct", "nov", "dec")

myPath = "c:\my documents\excel\"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

Set AllWkbk = Workbooks.Add(1)
ActiveSheet.Name = "deletemelater"

For iCtr = LBound(myWorkbookNames) To UBound(myWorkbookNames)
tempWkbkName = myPath & myWorkbookNames(iCtr) & ".xls"
If Dir(tempWkbkName) = "" Then
MsgBox tempWkbkName & " doesn't exist!"
Else
Set tempWkbk = Workbooks.Open(tempWkbkName)
For Each wks In tempWkbk.Worksheets
If LCase(wks.Name) = LCase("pivot") Then
'do nothing
Else
wks.Copy _
after:=AllWkbk.Worksheets(AllWkbk.Worksheets.Count)
End If
Next wks
tempWkbk.Close savechanges:=False
End If
Next iCtr

If AllWkbk.Worksheets.Count > 1 Then
Application.DisplayAlerts = False
AllWkbk.Worksheets("deletemelater").Delete
Application.DisplayAlerts = True
Else
AllWkbk.Close savechanges:=False
MsgBox "nothing imported!"
End If

End Sub
 
Top