Copper Contributor

I have a very basic understanding of Excel and need an answer to a question in the clearest terms possible. Step-by-step if possible. Or at least direction in what the function I am trying to accomplish is called so I can Google it.

In as brief a nutshell as I can: I have exported a customer survey from Sogolytics into Excel. One of the survey questions asks respondents to provide their name, address, and contact information. Each bit of data - name, company, address, email address, phone number – have their own separate field on the survey form. When exported into Excel, the responses show up like the following two examples:

 First name John Last name Smith Company ABC Corp Email email address removed for privacy reasons

 First name Mary Last name Jones Company XYZ Corp Email email address removed for privacy reasons

I was expecting (hoping) the data to appear in Excel as individual columns of data under their respective field header, like this:

 First name Last name Company Email John Smith ABC Corp email address removed for privacy reasons Mary Jones XYZ Corp email address removed for privacy reasons,

How do I tell Excel to take the information in the first two examples and arrange the data accordingly as I have shown in the third example? What is this process/function called? I could copy and paste the data, but I have close to 700 individual responses. Surely there is a way to have Excel do this.

Thanks for any guidance or advice you can give me.

John M.

5 Replies

You can try Power Query aka Get and Transform. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

Thank you. I will try that.

Oh, and just so everyone knows, I totally made up the names, companies and email addresses I used in my examples. I know not to violate anyone's privacy.

Alternative if you run Excel 2021 or 365:

With data in A1:B8 as above:

``=INDEX(B1:B8, SEQUENCE(ROWS(B1:B8)/4, 4))``

``````Sub transform()

Dim h, i, j, k, l, m As Long

Range("D2:G1048576").Clear

j = 2
k = 2
l = 2
m = 2

h = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To h
Select Case Cells(i, 1).Value

Case Is = "first name"
Cells(j, 4).Value = Cells(i, 2).Value
j = j + 1

Case Is = "last name"
Cells(k, 5).Value = Cells(i, 2).Value
k = k + 1

Case Is = "birthday"
Cells(l, 6).Value = Cells(i, 2).Value
l = l + 1

Case Is = "email"
Cells(m, 7).Value = Cells(i, 2).Value
m = m + 1

End Select

Next i

End Sub``````

Another alternative could be these lines of code. In the attached file you can click the button in cell I2 to run the macro.