May 12 2023 12:08 PM
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 address removed for privacy reasons |
First name | Mary |
Last name | Jones |
Company | XYZ Corp |
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 | |
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.
May 12 2023 12:51 PM
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.
May 12 2023 03:04 PM
May 12 2023 03:06 PM
May 12 2023 10:31 PM
Alternative if you run Excel 2021 or 365:
With data in A1:B8 as above:
=INDEX(B1:B8, SEQUENCE(ROWS(B1:B8)/4, 4))
May 13 2023 05:11 AM
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.