Search and summarize data

ajitt

New Member
Joined
Jun 28, 2010
Messages
5
Hi,

I am trying to search and summarize data from multiple worksheets to one.

I am tracking the scores of different students on different projects at different months. Each of the project is in its own sheet. I have attached a workbook with 3 project sheets.

I was to summarize the data in a summary sheet. Say I want to look at the performance of each of the student over the next 12 months, I want to search for this month say Aug-2010 in the array in each of the projects (loop through all sheets) and then sum up for Student 1 say where ever there is Aug-2010 and repeat that for all students. The array for each of the products will be constant.

I have been trying writing macros with HLOOKUP and ISNA. But, to not avail. Any help will be very much appreciated.

Could not find a way to upload the file. I have added to my google docs and the link is given below.

Sample.xls
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
ajitt,


Sample worksheets:


Excel Workbook
ABCDEFGH
3Aug-10Feb-11Mar-11Apr-11May-11Jun-11
4Student 1543567
5Student 2633141
6Student 3248562
7
Banjo





Excel Workbook
ABCDEFGH
3Aug-10Sep-11Oct-11Nov-11Dec-11Jan-12
4Student 1266365
5Student 2375141
6Student 3248562
7
Gaud





Excel Workbook
ABCDEFGH
3Mar-11Apr-11May-11Jun-11Jul-11Aug-11
4Student 1266365
5Student 2375141
6Student 3248562
7
City





Summary worksheet before the macro (the dates in row 3 must be manually entered, and not entered by a formula):


Excel Workbook
ABCDEFGHIJKLMN
3Aug-10Sep-10Oct-10Nov-10Dec-10Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11
4Student 1
5Student 2
6Student 3
7
Summary





Summary worksheet after the macro:


Excel Workbook
ABCDEFGHIJKLMN
3Aug-10Sep-10Oct-10Nov-10Dec-10Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11
4Student 17451112106
5Student 29368924
6Student 3441091476
7
Summary





Summary worksheet after the macro with more dates:


Excel Workbook
ABCDEFGHIJKLMNOPQRST
3Aug-10Sep-10Oct-10Nov-10Dec-10Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11Nov-11Dec-11Jan-12
4Student 17451112106566365
5Student 29368924175141
6Student 3441091476248562
7
Summary





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Option Base 1
Sub CreateSummary()
' hiker95, 08/16/2010, ME2411699
Dim ws As Worksheet, S As Worksheet
Dim LR As Long, LC As Long, a As Long, b As Long
Dim wsR As Long, wsC As Long
Dim Sary
Dim c As Range, d As Range, firstaddress As String
Application.ScreenUpdating = False
Set S = Worksheets("Summary")
LR = S.Cells(Rows.Count, 1).End(xlUp).Row
LC = S.Cells(3, Columns.Count).End(xlToLeft).Column
S.Range(S.Cells(4, 2), S.Cells(LR, LC)).ClearContents
Sary = S.Range(S.Cells(3, 1), S.Cells(LR, LC))
For Each ws In Sheets
  If ws.Name <> "Summary" Then
    For a = LBound(Sary) + 1 To UBound(Sary)
      firstaddress = ""
      With ws.Columns(1)
        Set c = .Find(Sary(a, 1), LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
          firstaddress = c.Address
          Do
            wsR = c.Row
            Set c = .FindNext(c)
          Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
      End With
      For b = 2 To LC Step 1
        firstaddress = ""
        With ws.Rows(3)
          Set d = .Find(Format(Sary(1, b), "mmm-yy"), LookIn:=xlValues, LookAt:=xlWhole)
          If Not d Is Nothing Then
            firstaddress = d.Address
            Do
              wsC = d.Column
              Sary(a, b) = Sary(a, b) + ws.Cells(wsR, wsC)
              Set d = .FindNext(d)
            Loop While Not d Is Nothing And d.Address <> firstaddress
          End If
        End With
      Next b
    Next a
  End If
Next ws
S.Activate
S.Range(S.Cells(3, 1), S.Cells(LR, LC)) = Sary
Erase Sary
Application.ScreenUpdating = True
End Sub


Then run the "CreateSummary" macro.
 
Upvote 0
Hiker95

Works great! Managed to write something up by myself as well. But my code has never been very efficient.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top