Combine multi tab event attendance data into one worksheet

[YellowTangerine]

Hello, I need to combine into one worksheet names of delegates who have attended training events. There will be a tab for each event with a list of delegate names. In several cases delegates have attended several events and in other cases only a specific event. I need to create a master worksheet with an alpha list of all delegates in the last 5 years showing which training event they attended.

I am no expert on Excel, but wondered if someone might be able to advise me on how best to achieve this?

Here's how I have set up the master worksheet and the data for each event will be in the tabs (shown at the bottom of the pic): There will eventually be a total of 35 events (columns on the master worksheet). The delegate name will appear once in column A and the columns for each event simply need an Y added for attending the event.

 

[OaklandJim]

Offering advice without a better understanding of the goal is challenging, for me at least. Might you provide a link to a version of the workbook with fake data and with an example of the result? Use the link icon above the message area. Use Dropbox, 1drive, etc. If you do that I'll try to assist.

 

[YellowTangerine]

Thank you. I will do that over the weekend. Much appreciated.

 

[YellowTangerine]

Hello OaklandJim, Here's a link to the fake data. The consolidated page is the end result I am looking for. If you look at the individual tabs per event I have manually linked through the delegates to the consolidated page, but this is not practical when having to do this for hundreds of delegates. Any ideas on how to create the consolidated without duplicates just to show which training event they attended would be really helpful for the charity I have been doing some work with. Thank you in advance for your support.
Sample Training Delegates 18-23.xlsx

 

[OaklandJim]

Got it. Here is the first issue. My sub starts by getting all names listed in the Events worksheets. That is how I intended to get the list of names for the Consolidated Data worksheet.

BUT, I did not get a name for Name D because it is not in any events worksheet.

So, my question is, are all of the names already listed in the Consolidated Data sheet? If so then my issue does not exist.

 

[OaklandJim]

I'm pretty close! I have assumed that all names are listed in the Consolidated Data worksheet.

I have a few questions.

I have to loop through all worksheets to identify Events worksheets. In your sample data the Event worksheets are named Event n. So, I only process worksheets whose name contains the word Event.

I suspect that for real data the tabs will be named for the event's name? If so I need a different way to determine if a given worksheet is an Event worksheet. I could look for the word "Delegate" (the column header for names in 4 of 5 Event worksheets) to do that. Said another way, will all Event worksheets contain the word Delegate? If so, will that word always be in cell A1 in the Events worksheets?

Another approach involves using the name of the Consolidated Data worksheet. I would loop through worksheets and process any that do not have that name. Will that worksheet name stay that way? Will there be any worksheets other than Events and Consolidated Data?

On a related note, the header for names in the Consolidated Data worksheet is Member Name. Will that stay that way?

 

[YellowTangerine]

Hello, thank you for looking at this. In response to your questions:

1. I can change the "Member" column name to delegate on the consolidated worksheet.

2. Each of the sheets will has a unique name according to the event and the consolidate sheet will hold a column header name for each event accordingly.

3. Delegates heading will be the same for each event in each worksheet

4. Delegates will always be in column A

Column A on the consolidated worksheet is is fact a running list of all delegates who have ever attended a training event since 2018. I have simply copied all the data into this column, removed duplicates and listed it alphabetically.

I hope this helps. I have been thinking it's too complex to create the consolidated form and that I may need to manually add the column data per event on the consolidated sheet, but if you think there is a way then I would love to try it. Thank you.

 

[RudRud]

=

Book2
	A	B	C	D	E	F
2		event 1	event 2	event 3	event 4	event 5
3	Name A	Name A	Name A	Name A
4	Name B		Name B			Name B
5	Name C	Name C	Name C	Name C	Name C	Name C
6	Name E	Name E
7	Name F			Name F
8	Name G	Name G			Name G
Consolidated Data

For now the code will start run from sheet 2 to last sheets (Sheet1 = "Consolidated Sheets")

Assuming all the list names starting from a2

From Consolidated Data Sheets Right click -> View code -> paste the code below to run

Please do let me know if have any adjustment / feedback

Option Explicit
Sub Test()
Dim dict As Object
Dim i%, m%, n%, j%, k%
Dim a As Variant
Dim t As Variant
Dim t2 As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Comparemode = vbTextCompare
ReDim b(1 To 5000, 1 To 6)

m = 1
For i = 2 To Worksheets.Count
        With Sheets(i)
           a = .Range("a2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
            j = j + 1
           b(1, j + 1) = Sheets(i).Name
         
           For k = 1 To UBound(a, 1)

                If Not dict.exists(a(k, 1)) Then
                    m = m + 1
                    n = m
                     dict.Add a(k, 1), n
                     b(n, 1) = a(k, 1)
                 Else
                    n = dict.Item(a(k, 1))
                 End If
               
                b(n, j + 1) = a(k, 1)
           Next k
         
        End With
Next i

Sheets("Consolidated Data").Select
[a2].Resize(m, Worksheets.Count).Value = b
[a2].Resize(m, Worksheets.Count).Sort Range("a3"), order1:=xlAscending, Header:=xlYes

End Sub

 

[YellowTangerine]

This looks amazing. Thank you. I will give it a try over the weekend. Much appreciated.

 

[YellowTangerine]

Actually, I just followed your instructions and added the code and got this pop up window when I clicked the play button to run it.