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 of the questions.
We are struggling with how to combine the data from multiple rows into one new row using Excel formulas and/or code vs. manually moving all of the input data into one row.
Once the data is in one row, we will be using Mail Merge to populate an aggregate document.
Sample Response Data Received:
Joe | Smith | 123456789 | Science | ||||
Joe | Smith | 123456789 | Other 1 | ||||
Joe | Smith | 123456789 | Math | ||||
Joe | Smith | 123456789 | Social Studies | ||||
Joe | Smith | 123456789 | ELA |
This is what we would like to have it look like when we are done combining:
Joe | Smith | 123456789 | Social Studies | ELA | Science | Math | Other 1 |
- Patrick2788Silver 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) )