+ Reply to Thread
Results 1 to 7 of 7

Need to create list based on name, range, and other variables

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    50

    Need to create list based on name, range, and other variables

    This one is a doozy for me. I've attached the sample workbook.

    I need to take the data that I have and create a list of students that have a grade below 75. That list needs to contain their coach's name, student IDs, student names, class failing (below 75), grade, and teacher's name.

    My first page has all the coaches names and the names and IDs of students on their roster.

    My second page has a large number of students with IDs, names, grades, class name, and teachers.

    My third page is where I'm putting it all together.

    A few things -
    I need to match the students based on ID, not name.
    Not all students names have a coach.
    I only need to show grades below 75.

    Any and all help is appreciated!!!

    New Failure Report.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need to create list based on name, range, and other variables

    in A3, use this, copied down...
    =IF(B3="","",IFERROR(INDEX('Coaches List'!$B$3:$B$37,MATCH('Failure Report'!B3,'Coaches List'!$C$3:$C$37,0)),"No Coach"))

    Then in B3, use this ARRAY formula, copied down and across as needed...
    =IFERROR(INDEX(Grades!A$2:A$202,SMALL(IF(Grades!$D$2:$D$202<75,ROW(Grades!$A$2:$A$202)-1),ROWS($A$1:A1))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Need to create list based on name, range, and other variables

    Hello,

    use Excel's built-in features like Tables and Pivot Tables.

    On the Grades sheet, select a cell in the table and click Insert > Table. Then type "Coach" into F1 and use this formula in F2:

    =IFNA(INDEX('Coaches List'!B:B,MATCH(Grades!A2,'Coaches List'!C:C,0)),"no coach")

    Since this is now an Excel Table, the formula will copy down automatically.

    With any cell of the table selected, click Insert > Pivot Table. Put it on a new worksheet or click on the Failure Report sheet in column A.

    Drag the desired fields into the Row area. In the Pivot Table Tools "Design" ribbon select the "Report Layout" with "Tabular Layout". Right-click each column of data and remove the subtotals.

    Finally, filter the Grade column to include only labels where the value is less than 75.

    You can now add more rows of data to the Grades table. Refresh the pivot table to show the new data.

    See attached file.

    cheers, teylyn
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Need to create list based on name, range, and other variables

    too slow...but since i made the file already
    similar to fdibbins answer
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Need to create list based on name, range, and other variables

    @humdingaling, how about you explain what you did so we don't need to open your file to find out? Search engines like words, too!
    Last edited by teylyn; 06-01-2015 at 11:53 PM.

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Need to create list based on name, range, and other variables

    i had the same formula as Fdibbins in e3
    using CSE
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    i also offered a different method using a CSE helper column
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then B3 normal formula (non CSE)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    in A3
    again similar to fdibbins but less outputs
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by humdingaling; 06-02-2015 at 12:14 AM. Reason: missing formula

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Need to create list based on name, range, and other variables

    See attached file with ARRAY formulas.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create List of Variables based on Unique Values in an Array
    By jmartineau in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2013, 06:02 PM
  2. Create Named List (range) based on check list
    By Hammer_757 in forum Excel General
    Replies: 4
    Last Post: 11-16-2011, 04:27 PM
  3. Create List of X Variables Repeated Y Times
    By nofzinger in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2010, 07:37 AM
  4. Help! Sum of a range based on two variables
    By sequence19 in forum Excel General
    Replies: 2
    Last Post: 06-18-2009, 01:39 PM
  5. Dynamically create variables based on sheet data
    By beeawwb in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-02-2007, 12:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1