Generate list from multiple sheets based on criteria

[jb]

Hello Helpers,
I have for example 4 sheets right now. First sheet "event_list" has list of sheet names under column title "Event Sheet Title".
Right now there 2 names in cell b4 and b5 - tennis and cricket respectively.

Note: There can be new sheets of new sports events can be added in future.
Also, in one sheet, there can be multiple entry of one student.

Now in sheet tennis and cricket, I have list of students from various classes who have participated in tennis and cricket event.

I want to generate consolidated list of students of particular class on last sheet "consolidate" based on name of class typed in C3 cell.
Here, it should display list of all the students of respective class who have participated in all the events in sorted order of Enrollment number.

This data is required to calculate leaves of students.

Sample calculation given in attached file.

 

[Monty]

Hello Helpers,
I have for example 4 sheets right now. First sheet "event_list" has list of sheet names under column title "Event Sheet Title".
Right now there 2 names in cell b4 and b5 - tennis and cricket respectively.

Note: There can be new sheets of new sports events can be added in future.
Also, in one sheet, there can be multiple entry of one student.

Now in sheet tennis and cricket, I have list of students from various classes who have participated in tennis and cricket event.

I want to generate consolidated list of students of particular class on last sheet "consolidate" based on name of class typed in C3 cell.
Here, it should display list of all the students of respective class who have participated in all the events in sorted order of Enrollment number.

This data is required to calculate leaves of students.

Sample calculation given in attached file.

Hello Jb

I have tried breaking down the steps into formulas:

Assuming your data is structured as follows:-Apologies I haven't seen your File.

- "event_list" sheet has the event names in column B starting from B4.
- Sheets "tennis" and "cricket" have student data with Enrollment Number in column A, Student Name in column B, and Class in column C.

On the "consolidate" sheet:

1. Class Lookup:
- In D4, enter the following formula to look up the class for the corresponding event in C3:

=IFERROR(VLOOKUP($B4, INDIRECT("'" & C$3 & "'!$B$4:$C$100"), 2, FALSE), "")

- Drag this formula across the table.

2.Student List by Class
- In E4, enter the following array formula to list students based on the class in D4:

=IFERROR(INDEX(INDIRECT("'" & D$4 & "'!$A$2:$C$100"), SMALL(IF(D$4=INDIRECT("'" & D$4 & "'!$C$2:$C$100"), ROW(INDIRECT("'" & D$4 & "'!$C$2:$C$100")-MIN(ROW(INDIRECT("'" & D$4 & "'!$C$2:$C$100")))+1)), ROW(1:1)), 1), "")

- This is an array formula, so after entering it, press `Ctrl + Shift + Enter` instead of just `Enter`. Drag this formula across the table.

3. Sort by Enrollment Number:
- In F4, use the `SORT` function to sort the list by Enrollment Number:
```excel

=SORT(FILTER($A$4:$B$100, $D$4:$D$100=C$3), 2, 1)

- Drag this formula across the table.

Remember to adjust the cell ranges based on the size of your data, and feel free to modify the formulas based on any specific requirements or variations in your data structure in excel file.

 

[p45cal]

In the attached file the event_list sheet isn't used.
I've converted the tables on the tennis and cricket sheets into proper Excel tables.
On the consolidate sheet I've made cell C3 into a Named range called Class.
I've set up a Power Query query which results in the table at cell A11 of the consolidate sheet. Currently this table will not update itself automatically (it can be arranged), it needs refreshing manually when you either change the data in the tennis and/or cricket sheets or add new sheets with similar tables for other events.
To see this in action, change the value in cell C3 of the consolidate sheet, go to the result table below, right-click somewhere in it and choose Refresh. That's it.

Something needs to be in place for this to work with new data tables:
The tables you want to include in the consolidation must have a name ending in event (upper/lower case doesn't matter). To see these table names and adjust them, select any cell within a table, at the top of the ribbon, you should see a Table Design tab, click that then on the left of the ribbon in the Properties section, you should see Table Name: below which you should see the name of the table. You can edit this (don't forget to press Enter after you've finished editing it). Any table you want to include in the consolidation should end with event as the last 5 characters (conversely, any table you do NOT want to be included should not end in those 5 characters).
What happens in Power Query is that all the Excel tables in the workbook are looked at but only those whose name ends in event are used, then it looks at the value in the named range Class (cell C3) on the consolidate sheet to decide what to show.
On the consolidate sheet I've left your 'desired results' just for comparison, they're not needed.

 

[AlanSidman]

In the attached. Loaded the Tennis and Cricket Tables into Power Query editor. Appended each to the other. Added a parameter query for the three types of classes. The look up/parameter filter for the particular class. Added a command button to auto refresh.

 

[jb]

Hello Jb

I have tried breaking down the steps into formulas:

Assuming your data is structured as follows:-Apologies I haven't seen your File.

- "event_list" sheet has the event names in column B starting from B4.
- Sheets "tennis" and "cricket" have student data with Enrollment Number in column A, Student Name in column B, and Class in column C.

On the "consolidate" sheet:

1. Class Lookup:
- In D4, enter the following formula to look up the class for the corresponding event in C3:

=IFERROR(VLOOKUP($B4, INDIRECT("'" & C$3 & "'!$B$4:$C$100"), 2, FALSE), "")

- Drag this formula across the table.

2.Student List by Class
- In E4, enter the following array formula to list students based on the class in D4:

=IFERROR(INDEX(INDIRECT("'" & D$4 & "'!$A$2:$C$100"), SMALL(IF(D$4=INDIRECT("'" & D$4 & "'!$C$2:$C$100"), ROW(INDIRECT("'" & D$4 & "'!$C$2:$C$100")-MIN(ROW(INDIRECT("'" & D$4 & "'!$C$2:$C$100")))+1)), ROW(1:1)), 1), "")

- This is an array formula, so after entering it, press `Ctrl + Shift + Enter` instead of just `Enter`. Drag this formula across the table.

3. Sort by Enrollment Number:
- In F4, use the `SORT` function to sort the list by Enrollment Number:
```excel

=SORT(FILTER($A$4:$B$100, $D$4:$D$100=C$3), 2, 1)

- Drag this formula across the table.

Remember to adjust the cell ranges based on the size of your data, and feel free to modify the formulas based on any specific requirements or variations in your data structure in excel file.

Hi helper,


Your first step is not working so i am unable to proceed.
On the "consolidate" sheet:

1. Class Lookup:
- In D4, enter the following formula to look up the class for the corresponding event in C3:

=IFERROR(VLOOKUP($B4, INDIRECT("'" & C$3 & "'!$B$4:$C$100"), 2, FALSE), "")

- Drag this formula across the table.

Kindly refer my file and suggest solution accordingly. I want consolidate sheet Class wise. My excel test file will represent my problem clearly. Thanks in advance dear helper.

 

[jb]

Hi helper,


Your first step is not working so i am unable to proceed.
On the "consolidate" sheet:

1. Class Lookup:
- In D4, enter the following formula to look up the class for the corresponding event in C3:

=IFERROR(VLOOKUP($B4, INDIRECT("'" & C$3 & "'!$B$4:$C$100"), 2, FALSE), "")

- Drag this formula across the table.

Kindly refer my file and suggest solution accordingly. I want consolidate sheet Class wise. My excel test file will represent my problem clearly. Thanks in advance dear helper.

you helped me with vba code. I inserted that code in my excel sheet which i have saved now as macro enabled worksheet. But not getting consolidated sheet.
Please help. Attaching new file with vba code. Here, I have tried adding one more sheet of sports.

 

[Monty]

Hi helper,


Your first step is not working so i am unable to proceed.
On the "consolidate" sheet:

1. Class Lookup:
- In D4, enter the following formula to look up the class for the corresponding event in C3:

=IFERROR(VLOOKUP($B4, INDIRECT("'" & C$3 & "'!$B$4:$C$100"), 2, FALSE), "")

- Drag this formula across the table.

Kindly refer my file and suggest solution accordingly. I want consolidate sheet Class wise. My excel test file will represent my problem clearly. Thanks in advance dear helper.

Hello Jb

I think to look up the class for the corresponding event in C3, you can use the VLOOKUP function. Assuming your "event_list" sheet has class names in column A and event names in column B, you can use the following formula in D4 of the "consolidate" sheet:

=IFERROR(VLOOKUP(C3, event_list!B:D, 3, FALSE), "")

This formula looks up the class name based on the event name in C3 from the "event_list" sheet and returns the corresponding class name. If there's an error (e.g., if the event name is not found), it will return an empty string.

Adjust the cell references based on the actual location of your data in the "event_list" sheet....Let me know

 

[jb]

@Monty sir, you have provided me below given solution for my problem of generating consolidated sheet based on multiple sheets. But it is not working.
Kindly help.

>>> You've noted many times <<<
>>> use code - tags <<<​

Option Explicit

Sub GenerateConsolidatedList()
    Dim wsEventList As Worksheet
    Dim wsConsolidated As Worksheet
    Dim lastRow As Long
    Dim eventCell As Range

    ' Set references to worksheets
    Set wsEventList = ThisWorkbook.Sheets("event_list")
    Set wsConsolidated = ThisWorkbook.Sheets("consolidated")

    ' Clear existing data in consolidated sheet
    wsConsolidated.Cells.Clear

    ' Loop through event names in "event_list" sheet
    For Each eventCell In wsEventList.Range("B4:B" & wsEventList.Cells(wsEventList.Rows.Count, "B").End(xlUp).Row)
        ' Add event name as a sheet
        'Sheets.Add(After:=Sheets(Sheets.Count)).Name = eventCell.Value

        ' Copy data from event sheet to consolidated sheet
        wsConsolidated.Cells(1, wsConsolidated.Cells(1, Columns.Count).End(xlToLeft).Column + 1).Value = eventCell.Value
        lastRow = Sheets(eventCell.Value).Cells(Sheets(eventCell.Value).Rows.Count, "A").End(xlUp).Row
        Sheets(eventCell.Value).Range("A2:D" & lastRow).Copy wsConsolidated.Cells(2, wsConsolidated.Cells(1, Columns.Count).End(xlToLeft).Column + 1)

        ' Delete the temporary event sheet
        Application.DisplayAlerts = False
        Sheets(eventCell.Value).Delete
        Application.DisplayAlerts = True
    Next eventCell
End Sub

 

[Monty]

Hello Jb

Can you help with the error code to modify the code for you

 

[jb]

Hello Jb

Can you help with the error code to modify the code for you

Sir,
Kindly refer attached file. I have already added your code. But the problem is, it is not generating any output in consolidated sheet.

Sir, I am entering my required class in F3 cell of "event_list" sheet.

Consolidated sheet must generate list of students of inputted class (in F3 cell ) participated in all the event names mentioned in "event_list" sheet.
I have given column titles in "consolidated" sheet in row 3.
From row 4 onwards, it should paste data from all the event sheets.

Also, the data must be displayed in sorted order of Enrollment number.

Also, when new sheet is added, user will enter name of sheet in "event_list" sheet.
So, accordingly consolidated sheet must be automatically updated.

 

[jb]

Hello Jb

Can you help with the error code to modify the code for you

I have attached file. Please help.

 

[Monty]

Sir,
Kindly refer attached file. I have already added your code. But the problem is, it is not generating any output in consolidated sheet.

Sir, I am entering my required class in F3 cell of "event_list" sheet.

Consolidated sheet must generate list of students of inputted class (in F3 cell ) participated in all the event names mentioned in "event_list" sheet.
I have given column titles in "consolidated" sheet in row 3.
From row 4 onwards, it should paste data from all the event sheets.

Also, the data must be displayed in sorted order of Enrollment number.

Also, when new sheet is added, user will enter name of sheet in "event_list" sheet.

So, accordingly consolidated sheet must be automatically updated.

JB- This code should copy the data from each event sheet, sort it by Enrollment number, and paste it into the consolidated sheet. Additionally, it deletes the temporary event sheet after copying the data. Please give it a try and let me know if it resolves your issue.

Option Explicit

Sub GenerateConsolidatedList()
    Dim wsEventList As Worksheet
    Dim wsConsolidated As Worksheet
    Dim lastRow As Long
    Dim eventCell As Range
    Dim consolidatedLastColumn As Long
    
    ' Set references to worksheets
    Set wsEventList = ThisWorkbook.Sheets("event_list")
    Set wsConsolidated = ThisWorkbook.Sheets("consolidated")
    
    ' Clear existing data in consolidated sheet
    wsConsolidated.Cells.Clear
    
    ' Loop through event names in "event_list" sheet
    For Each eventCell In wsEventList.Range("B4:B" & wsEventList.Cells(wsEventList.Rows.Count, "B").End(xlUp).Row)
        ' Add event name as a sheet
        ' Sheets.Add(After:=Sheets(Sheets.Count)).Name = eventCell.Value
        
        ' Copy data from event sheet to consolidated sheet
        consolidatedLastColumn = wsConsolidated.Cells(1, wsConsolidated.Columns.Count).End(xlToLeft).Column + 1
        wsConsolidated.Cells(1, consolidatedLastColumn).Value = eventCell.Value
        
        ' Find last row in event sheet
        lastRow = Sheets(eventCell.Value).Cells(Sheets(eventCell.Value).Rows.Count, "A").End(xlUp).Row
        
        ' Copy data and sort by Enrollment number
        Sheets(eventCell.Value).Range("A2:D" & lastRow).Copy
        wsConsolidated.Cells(2, consolidatedLastColumn).PasteSpecial Paste:=xlPasteValues
        wsConsolidated.Cells(2, consolidatedLastColumn).PasteSpecial Paste:=xlPasteFormats
        wsConsolidated.Sort.SortFields.Clear
        wsConsolidated.Sort.SortFields.Add Key:=wsConsolidated.Range(wsConsolidated.Cells(3, consolidatedLastColumn), wsConsolidated.Cells(lastRow + 1, consolidatedLastColumn)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With wsConsolidated.Sort
            .SetRange wsConsolidated.Range(wsConsolidated.Cells(2, 1), wsConsolidated.Cells(lastRow + 1, consolidatedLastColumn))
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        ' Delete the temporary event sheet
        Application.DisplayAlerts = False
        Sheets(eventCell.Value).Delete
        Application.DisplayAlerts = True
    Next eventCell
End Sub

 

[Marc L]

Hello, according to the initial post attachment, like any Excel user operating manually with the Macro Recorder on,​

for starters a VBA event procedure fired by cell C3 so to obviously paste only to the Consolidate worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address <> "$C$3" Then Exit Sub
        Application.EnableEvents = False
        [A5].CurrentRegion.Offset(1).Clear
     If Not IsEmpty(Target) Then
        Application.ScreenUpdating = False
    For S% = 2 To Index - 1
   With Sheets(S).[A3].CurrentRegion.Columns
     If IsNumeric(Application.Match(Target, .Item(3), 0)) Then
       .AutoFilter 3, Target
       .Item("B:I").Offset(1).Copy Cells(Rows.Count, 2).End(xlUp)(2)
       .AutoFilter
     End If
   End With
    Next
        [A5].CurrentRegion.Sort [E5], 1, Header:=1
        Application.ScreenUpdating = True
     End If
        Application.EnableEvents = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[jb]

JB- This code should copy the data from each event sheet, sort it by Enrollment number, and paste it into the consolidated sheet. Additionally, it deletes the temporary event sheet after copying the data. Please give it a try and let me know if it resolves your issue.

Option Explicit

Sub GenerateConsolidatedList()
    Dim wsEventList As Worksheet
    Dim wsConsolidated As Worksheet
    Dim lastRow As Long
    Dim eventCell As Range
    Dim consolidatedLastColumn As Long
   
    ' Set references to worksheets
    Set wsEventList = ThisWorkbook.Sheets("event_list")
    Set wsConsolidated = ThisWorkbook.Sheets("consolidated")
   
    ' Clear existing data in consolidated sheet
    wsConsolidated.Cells.Clear
   
    ' Loop through event names in "event_list" sheet
    For Each eventCell In wsEventList.Range("B4:B" & wsEventList.Cells(wsEventList.Rows.Count, "B").End(xlUp).Row)
        ' Add event name as a sheet
        ' Sheets.Add(After:=Sheets(Sheets.Count)).Name = eventCell.Value
       
        ' Copy data from event sheet to consolidated sheet
        consolidatedLastColumn = wsConsolidated.Cells(1, wsConsolidated.Columns.Count).End(xlToLeft).Column + 1
        wsConsolidated.Cells(1, consolidatedLastColumn).Value = eventCell.Value
       
        ' Find last row in event sheet
        lastRow = Sheets(eventCell.Value).Cells(Sheets(eventCell.Value).Rows.Count, "A").End(xlUp).Row
       
        ' Copy data and sort by Enrollment number
        Sheets(eventCell.Value).Range("A2:D" & lastRow).Copy
        wsConsolidated.Cells(2, consolidatedLastColumn).PasteSpecial Paste:=xlPasteValues
        wsConsolidated.Cells(2, consolidatedLastColumn).PasteSpecial Paste:=xlPasteFormats
        wsConsolidated.Sort.SortFields.Clear
        wsConsolidated.Sort.SortFields.Add Key:=wsConsolidated.Range(wsConsolidated.Cells(3, consolidatedLastColumn), wsConsolidated.Cells(lastRow + 1, consolidatedLastColumn)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With wsConsolidated.Sort
            .SetRange wsConsolidated.Range(wsConsolidated.Cells(2, 1), wsConsolidated.Cells(lastRow + 1, consolidatedLastColumn))
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
       
        ' Delete the temporary event sheet
        Application.DisplayAlerts = False
        Sheets(eventCell.Value).Delete
        Application.DisplayAlerts = True
    Next eventCell
End Sub

Hello Monty sir, I tried your code. But,
1. It deletes the events sheets. I need to keep the events sheets.
2. In the consolidated sheet, 3rd row is header row. It should remains as it is. Data should be pasted from 4th row.
3. It should paste column B to column I from each event sheet based on class name mentioned in F3 cell of event_list sheet.

Your solution is not giving me desired result. Pasting my file again with this new code.

 

[jb]

Hello Monty sir, I tried your code. But,
1. It deletes the events sheets. I need to keep the events sheets.
2. In the consolidated sheet, 3rd row is header row. It should remains as it is. Data should be pasted from 4th row.
3. It should paste column B to column I from each event sheet based on class name mentioned in F3 cell of event_list sheet.

Your solution is not giving me desired result. Pasting my file again with this new code.

Sir, different teacher may type different class name in F3 cell of event_list sheet. And based on their class name, it should fetch data from all event sheets and generate list of students of the respective class in consolidated sheet.
For this, event sheets can not be deleted.

Sir, you have helped me a lot. Kindly refer the attached file and help me. Waiting for your reply.

 

[jb]

Sir, different teacher may type different class name in F3 cell of event_list sheet. And based on their class name, it should fetch data from all event sheets and generate list of students of the respective class in consolidated sheet.
For this, event sheets can not be deleted.

Sir, you have helped me a lot. Kindly refer the attached file and help me. Waiting for your reply.

Hello Monty sir,

I have solved almost all issues that I was facing. I have done changes to your code.
Now, my event sheets are not deleted.
My data started being pasted from row 4 in consolidated sheet.
My data is sorted on enrollment number in consolidated sheet.

But I could not find answer of following 2 issues:

1. Consolidated sheet contains all the rows from all the event sheets. While, I want to copy only those rows where class name (column B) matches with cell F3 value of event_list sheet. For e.g. If I type SYBCOM in F3 cell of event_list sheet, then consolidated sheet must contain rows with class name as SYBCOM.
2. I want the consolidated list to be sorted on enrollment number (column D) and if enrollment number is same then on event name (Column F).

Kindly refer updated sheet.

 

[Marc L]

But I could not find answer of following 2 issues

Wrong as you already had a working VBA solution ! If you only well follow and read your thread … ​

​

 

[jb]

Wrong as you already had a working VBA solution ! If you only well follow and read your thread … ​

​

I received VBA code from Monty sir in this forum for my problem.
I tried to understand the code and make changes in VBA code to suit to my requirement.
I tried a lot but I couldn't implement above mentioned 2 features.
So I am asking for help.

 

[Marc L]

As I did not state for a working VBA solution from Monty …​

​

 

[jb]

Thank you all.
I finally could create my required VBA code after searching a lot and many times trial and error method.
Now my VBA code is working perfectly as per my requirement.
@Monty sir, your VBA code gave me strong base. Thank you once again.