Forum Discussion
How do I change a table with 2 columns where I have repeating values in both columns into a matrix?
Hopefully I will be able to explain my problem correctly. I have a table with two columns in it. Column A has students in it, column B has the subject that each student is taking. Both column A a...
RMF40 If you have Excel for Microsoft 365, you could try something along these lines (using the new PIVOTBY function):
=LET(
a, FILTER(A2:B1000,A2:A1000<>""),
r, CHOOSECOLS(a,1),
v, CHOOSECOLS(a,2),
n, ROWS(r),
c, "Subject "&CHAR(MMULT((SEQUENCE(n)>=SEQUENCE(,n))*(r=TOROW(r)),EXPAND(1,n,,1))+64),
PIVOTBY(r,c,v,CONCAT,0,0,,0)
)
See attached...