Creating a register per class from a master list of all classes/clubs?

[Collington]

Hi folks, I (and my school) need some help again pretty please!

We have a list of all the children who attend after-school clubs in one worksheet:

In a bunch of other worksheets (one per class) in the same workbook we'd like it to list the day/kid/club for that class so it looks like this:

*cell A1 is, on it's own, the class name exactly as it should be on the master list, if that helps you just do a simple cell reference.

I've tried hacking an amazing formula Fluff helped me with before, (exciting IFERROR, INDEX, ROW, divide, multiply magic) but I'm just not clever enough to mutate it to meet these needs. Any hints/tips/help?

Thank you for your help!


*For the sake of GDPR I just want to say these aren't real clubs or real children. It's totally just dummy data.

 

[Fluff]

How about

Book1
	A	B	C
1	Aardvarks
2	Day	Child	Club
3	Tuesday	Lola	Musical Theatre
4	Tuesday	Zack	Musical Theatre
5	Monday	Nancy	Tai Chi
6	Tuesday	Nancy	Tai Chi
7
8
Aardvarks

Cell Formulas
Range	Formula
A3:B8	A3	=IFERROR(INDEX(List!B$2:B$10,AGGREGATE(15,6,(ROW(List!B$2:B$10)-ROW(List!B$2)+1)/(List!$D$2:$D$10=$A$1),ROWS(A$4:A4))),"")
C3:C8	C3	=IFERROR(INDEX(List!A$2:A$10,AGGREGATE(15,6,(ROW(List!A$2:A$10)-ROW(List!A$2)+1)/(List!$D$2:$D$10=$A$1),ROWS(C$4:C4))),"")

PS, in future please use the XL2BB add-in to post data, rather than an image. It saves us recreating you data.

 

[dmt32]

Hi,
If you don't mid using VBA then try code below & see if helps you

Place following code in a STANDARD module ( Alt+F11 Insert > Module)

Option Explicit
Sub Split_Class()
    Dim wsData As Worksheet, wsNames As Worksheet, wsFilter As Worksheet
    Dim Datarng As Range, FilterRange As Range
    Dim rowcount As Long
    Dim FilterCol As Variant
   
    On Error GoTo progend
   
'******************************************************************************************************

'your master sheet
    Set wsData = ActiveSheet
   
'Column you are filtering
    FilterCol = "D"
   
'******************************************************************************************************
   
    With Application
        .ScreenUpdating = False: .DisplayAlerts = False
    End With
'add filter sheet
    Set wsFilter = ThisWorkbook.Worksheets.Add
   
    With wsData
        .Activate
'add password if needed
        .Unprotect Password:=""
       
        Set Datarng = .Range("A1").CurrentRegion
       
'extract values from FilterCol'to filter sheet
        .Cells(1, FilterCol).Resize(Datarng.Rows.Count).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=wsFilter.Range("A1"), Unique:=True
       
        rowcount = wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp).Row
'set Criteria
        wsFilter.Range("B1").Value = wsFilter.Range("A1").Value
       
        For Each FilterRange In wsFilter.Range("A2:A" & rowcount)
'check for blank cell in range
            If FilterRange.Value <> "" Then
'add the FilterRange to criteria
                wsFilter.Range("B2").Value = FilterRange.Value
'check if sheet exists
                If Not Evaluate("ISREF('" & FilterRange.Value & "'!A1)") Then
'add new sheet
                    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = FilterRange.Value
                End If
'set object variable to sheet
                Set wsNames = Worksheets(FilterRange.Value)
'clear sheet
                wsNames.UsedRange.Clear
'copy data
                Datarng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsFilter.Range("B1:B2"), _
                CopyToRange:=wsNames.Range("A1"), Unique:=False
            End If
'autofit columns
            wsNames.UsedRange.Columns.AutoFit
'clear from memory
            Set wsNames = Nothing
        Next
        .Select
    End With
progend:
    wsFilter.Delete
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True
    End With
    If Err <> 0 Then
        MsgBox (Error(Err)), vbCritical, "Error"
        Err.Clear
    End If
End Sub

Code should split data by class (column D) to a sheet with same name - If that sheet does not exist, one will be created

Note: Master sheet must be activesheet when code is run

Hope Helpful

Dave

 

[Collington]

THANK YOU BOTH!

Fluff's suggestion totally worked so I didn't even need to try dmt32's - but it looked awesome!

And I'm sorry about the screenshot. I did read this thread: Welcome to Version 4 of the New MrExcel Message Board! and it did mention putting in a screenshot so that's what I did. But I can absolutely see why I should have gone with the XL2BB and will do that in the future!

 

[Fluff]

Glad we could help & thanks for the feedback