Merge Records of Text in Excel

[Chad Vance]

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

[#2]

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