Merge Records of Text in Excel

Copper Contributor

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:

JoeSmith123456789  Science  
JoeSmith123456789    Other 1
JoeSmith123456789   Math 
JoeSmith123456789Social Studies    
JoeSmith123456789 ELA   


This is what we would like to have it look like when we are done combining:

JoeSmith123456789Social StudiesELAScienceMathOther 1
1 Reply

@Chad Vance 

You can use REDUCE to  accomplish this task. 

    ID, INDEX(matrix, , 3),
    UniqueID, SORT(UNIQUE(ID)),
    Pivot, LAMBDA(a, v,
            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)