Output names to a grid comparing two data points

[Trumpetnut]

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

[#2 · Accepted solution]

@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.

[#3]

Hi Hans, you utterly magnificent Excel beast. Thank you so much. That has worked perfectly.

Kind Regards

Simon

[#4]

@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...

[#5]

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.

[#6]

@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 @Hans Vogelaar 's method with a few small adjustments. Try the newly attached file and see if it works in your version of Excel... Cheers!