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]

Sorry, the more recent workbook is HERE. It uses checkmarks. Let me know if it does what is needed.

 

[YellowTangerine]

I resolved the issue of blank workbook. Am still trying to get the code to work.

 

[YellowTangerine]

It occurred to me that instead of Y indicating attendance that it would be more elegant to use a check mark. I also centered the character. If that sounds good then replace

'                   Put Y into the cell for the name found for the Event.
                    rAnchorCellConsolidated.Offset(iName, iEventColumnNumber) = "Y"

With this

'                   Put check mark into the cell for the name found for the Event. Center it.
                    With rAnchorCellConsolidated.Offset(iName, iEventColumnNumber)
                        .VerticalAlignment = xlCenter
                        .HorizontalAlignment = xlCenter
                        .Value = "ü"
                        .Font.Name = "Wingdings"
                    End With

Where do I insert this code please?

 

[YellowTangerine]

Sorry, the more recent workbook is HERE. It uses checkmarks. Let me know if it does what is needed.

Yes, this is good. Thank you. I have run the code, but not all columns are populating and some names appear along the rows correctly and others in random places. the first column is alphabetically listed. The check marks will be fine if they correctly link to the names so for now I can see using the code where the name is carried across that this is not working. I don't want to use up anymore of your time. I think I shall have to do this all manually. I am every so grateful for your support. Thank you.

 

[OaklandJim]

All code is in the workbook. You do not need to add any code to another workbook. See the most recent workbook HERE. It does what you asked for, for me at least! I'm happy to get it to work correctly.

Might you provide a link to the workbook giving you grief?

 

[RudRud]

I ran a few of the codes and either they error or I get this showing..... The last but one code shows like this

Book5
	A	B	C	D	E	F
1
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

Hi @YellowTangerine, You may re-run the code that i posted above post#11

Are you using in MacOs Version Currently? If Yes Please import dictionary.cls into class module in order to access dictionary in MacOS as the step below:

Releases · VBA-tools/VBA-Dictionary

Drop-in replacement for Scripting.Dictionary on Mac - VBA-tools/VBA-Dictionary

github.com

click source code zip

After imported dictionary.cls into class module,

Right Click Consolidated Data - sheets -> View Code -> Paste below code

Option Explicit
Sub Test()
On Error Resume Next
Dim i%, m%, n%, j%, k%
Dim a As Variant
Dim t As Variant
Dim t2 As Variant
Dim dict as new 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]

Hello again. I am really grateful for your support. I will try this on another project next time it comes round, which will be in a few months. I am running a MAC and that is likely the cause of the issue, so I am going to save this and follow the instructions you have kindly posted above. I will have more time to look at this more closely than. I am looking forward to being able to automate this. I will get back to you to let you know how I get on. Huge thanks once again.

 

[RudRud]

Hello again. I am really grateful for your support. I will try this on another project next time it comes round, which will be in a few months. I am running a MAC and that is likely the cause of the issue, so I am going to save this and follow the instructions you have kindly posted above. I will have more time to look at this more closely than. I am looking forward to being able to automate this. I will get back to you to let you know how I get on. Huge thanks once again.

Glad to assist, If you have any further questions, please don't hesitate to ask/share with us