Forum Discussion
Chad Vance
Oct 03, 2023Copper Contributor
Merge Records of Text in Excel
We are collecting student input from multiple individuals via a MS Form as text data. Each individual enters text data into the Form questionnaire for their area of expertise while ignoring the rest ...
Patrick2788
Oct 09, 2023Silver Contributor
You can use REDUCE to accomplish this task.
=LET(
ID, INDEX(matrix, , 3),
UniqueID, SORT(UNIQUE(ID)),
Pivot, LAMBDA(a, v,
LET(
filtered, FILTER(matrix, ID = v),
name_and_ID, TAKE(filtered, 1, 3),
subjects, TOROW(DROP(filtered, , 3), 1, 1),
VSTACK(a, HSTACK(name_and_ID, subjects))
)
),
DROP(REDUCE("", UniqueID, Pivot), 1)
)