Summary Table from Other Worksheets

[fixthis]

I am in need to create a summary table of cumulative points for each participant that has entered in monthly competitions. Currently I have a summary worksheet that I have to manually enter names when new members join and submits to the competition. I am looking for a way to automatically populate the summary table Name column D base on whether the name shows up in any of the worksheets named Jan 2015 through Nov 2015 in range L12:L40 (this range varies in size). I have created a fictitious competition for Jan and Feb worksheets for purposes of testing.

I am working in Excel 2003.

Cross posted at http://www.mrexcel.com/forum/excel-questions/839331-creating-sub-table-based-presence-other-tables.html

Thanks

 

[Deepak]

Can someone help with this problem?

Macro Acceptable ??

 

[fixthis]

I am not good at Macros, but it would be worth a try.

 

[Deepak]

Pls check this...

Option Explicit

Sub test()
Dim ws As Worksheet, r As Range

Application.ScreenUpdating = False
With CreateObject("scripting.dictionary")
    For Each ws In ThisWorkbook.Worksheets
        If IsDate(ws.Name) Then
            For Each r In ws.Range("L12:L40")
                If Len(r) > 0 And Not .exists(r.Value) Then .Add r.Value, 1
            Next
        End If
    Next
    Sheets("2015 Summary by Category").Range("D6:D52") = ""
    Sheets("2015 Summary by Category").Range("D6").Resize(.Count).Value = Application.Transpose(.keys)
End With
Application.ScreenUpdating = True

End Sub

 

[fixthis]

Thank you Deepak that worked spot on.

Would it be possible to extend that same idea to grab the totals in column H of each worksheet for the names by category? Either in a separate code (macro) or in the same code.

 

[Deepak]

Thank you Deepak that worked spot on.

Would it be possible to extend that same idea to grab the totals in column H of each worksheet for the names by category? Either in a separate code (macro) or in the same code.

Little more specific with a sample is req.

 

[fixthis]

Using the attached file in the first post.
At Worksheet "2015 Summary by Category", for each name in column D, I would like to sum the corresponding total points in column H from all worksheets having month and year (i.e., Jan 2015) for the corresponding name (produced by your Macro above) and Category.

For example: Adam (row 6) will have 4 points under Nature because there is a 4 at cell H47 on the Feb 2015 worksheet where his name and the code NA (for Nature Advanced) is used to create a unique combination. Currently I used the following formula to do this.

=SUMPRODUCT(SUMIF(INDIRECT("'"&ShtList&"'!X9:X120"),LEFT(G$5,1)&"*|"&$D6,INDIRECT("'"&ShtList&"'!H9:H120")))

So I am looking for a macro that can basically do what the formula does above hopefully without needing the helper column (hidden column X) on each worksheet.

ShtList is a list of the sheet names Jan 2015, Feb 2015 etc.