Forum Discussion

Chad Vance's avatar
Chad Vance
Copper Contributor
Oct 03, 2023

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:

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
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    Chad Vance 

    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)
    )

Resources