Forum Discussion
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 ...
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)
)