Forum Discussion

Trumpetnut's avatar
Trumpetnut
Copper Contributor
May 07, 2024

Output names to a grid comparing two data points

Hi

I have a table with columns for student names and assessment scores for multiple assessments, each graded 1 to 9. I also have a tracking table where grades for one assessment go horizontally and a second set of grades go vertically. I am looking for some help devising a formula that will output any of the names from the data table that have assessment grades, (for example Assessment 1 is 9,and assessment 2 is 9), to the appropriate cell in the grid where 9 and 9 intersect.

 

Thank you for any help you can offer.

 

Simon

  • Trumpetnut 

    In C4 on the Tracking Grid sheet:

     

    =TEXTJOIN(", ", TRUE, FILTER('Raw Data'!$A$2:$A$100, ('Raw Data'!$B$2:$B$100=C$3)*('Raw Data'!$C$2:$C$100=$B4), ""))

     

    Adjust the ranges if the data on Raw Data extend below row 100.

    Fill to the right and down.

  • Trumpetnut 

    In C4 on the Tracking Grid sheet:

     

    =TEXTJOIN(", ", TRUE, FILTER('Raw Data'!$A$2:$A$100, ('Raw Data'!$B$2:$B$100=C$3)*('Raw Data'!$C$2:$C$100=$B4), ""))

     

    Adjust the ranges if the data on Raw Data extend below row 100.

    Fill to the right and down.

    • Trumpetnut's avatar
      Trumpetnut
      Copper Contributor
      Hi Hans, you utterly magnificent Excel beast. Thank you so much. That has worked perfectly.

      Kind Regards

      Simon
      • Trumpetnut's avatar
        Trumpetnut
        Copper Contributor
        Hi Hans. Following on from the above solution, do you know of a way to have the names appear in the grid with an associated hyperlink? So, someone can look at the grid and click on the name that will take them to a linked document?

        Kind Regards

        Simon
  • djclements's avatar
    djclements
    Bronze Contributor

    Trumpetnut Just for fun, here's a dynamic variant for MS365:

     

    Assessment Tracking Grid

     

    The formula in cell B2 is:

     

    =LET(
        d, DROP(tblData,, 1),
        s, HSTACK(SEQUENCE(1, 9, 9, -1), "IE"),
        n, COLUMNS(s),
        a, MAKEARRAY(n, n, LAMBDA(r,c,
            TEXTJOIN(CHAR(10),, FILTER(tblData[Name], (INDEX(d,, C1)=INDEX(s, c))*(INDEX(d,, A3)=INDEX(s, r)), "")))),
        VSTACK(HSTACK("Score", s), HSTACK(TOCOL(s), a))
    )

     

    Simply change the assessment numbers in the orange-shaded cells (C1 & A3) to compare number 1 & 3 or 2 & 3. The source data in this example has been formatted as a structured Excel table named tblData. The Line Feed character (10) was used as the delimiter for TEXTJOIN and Wrap Text formatting was applied to the output grid, so each name appears on a new line... adjust the row height of each row as needed to display all of the names in each cell.

     

    Note: if you don't want to display "IE" in the output grid, just change the definition for the s variable to SEQUENCE(1, 9, 9, -1)

     

    See attached example workbook...

    • Trumpetnut's avatar
      Trumpetnut
      Copper Contributor
      Wow. Thank you for this. I do have to say that it is not just a little bit, but light years ahead of any excel understanding I have, but I can see that it works in the file I have downloaded. The only issue I have is that when I add in any new raw data in the table it says that the formula has an unsupported function.
      • djclements's avatar
        djclements
        Bronze Contributor

        Trumpetnut That's unfortunate. The single-cell dynamic array formula will only work in Excel for MS365 or Excel for the web. I'm guessing you're using either Excel 2019 or 2021?

         

        The same layout and functionality should still be achievable using HansVogelaar 's method with a few small adjustments. Try the newly attached file and see if it works in your version of Excel... Cheers!