Forum Discussion
Output names to a grid comparing two data points
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 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...
- 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.
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!
