Search and summarize data

[ajitt]

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

 

[hiker95]

ajitt,


Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[hiker95]

ajitt,

Got the data - let me have a look.

 

[ajitt]

Thanks! I am looking forward to the possible solution.

 

[hiker95]

ajitt,


Sample worksheets:

Excel Workbook
	A	B	C	D	E	F	G	H
3		Aug-10	Feb-11	Mar-11	Apr-11	May-11	Jun-11
4	Student 1	5	4	3	5	6	7
5	Student 2	6	3	3	1	4	1
6	Student 3	2	4	8	5	6	2
7
Banjo

Excel Workbook
	A	B	C	D	E	F	G	H
3		Aug-10	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12
4	Student 1	2	6	6	3	6	5
5	Student 2	3	7	5	1	4	1
6	Student 3	2	4	8	5	6	2
7
Gaud

Excel Workbook
	A	B	C	D	E	F	G	H
3		Mar-11	Apr-11	May-11	Jun-11	Jul-11	Aug-11
4	Student 1	2	6	6	3	6	5
5	Student 2	3	7	5	1	4	1
6	Student 3	2	4	8	5	6	2
7
City

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

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3		Aug-10	Sep-10	Oct-10	Nov-10	Dec-10	Jan-11	Feb-11	Mar-11	Apr-11	May-11	Jun-11	Jul-11
4	Student 1
5	Student 2
6	Student 3
7
Summary

Summary worksheet after the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
3		Aug-10	Sep-10	Oct-10	Nov-10	Dec-10	Jan-11	Feb-11	Mar-11	Apr-11	May-11	Jun-11	Jul-11
4	Student 1	7						4	5	11	12	10	6
5	Student 2	9						3	6	8	9	2	4
6	Student 3	4						4	10	9	14	7	6
7
Summary

Summary worksheet after the macro with more dates:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
3		Aug-10	Sep-10	Oct-10	Nov-10	Dec-10	Jan-11	Feb-11	Mar-11	Apr-11	May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11	Dec-11	Jan-12
4	Student 1	7						4	5	11	12	10	6	5	6	6	3	6	5
5	Student 2	9						3	6	8	9	2	4	1	7	5	1	4	1
6	Student 3	4						4	10	9	14	7	6	2	4	8	5	6	2
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.

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.

 

[ajitt]

Hiker95

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