Forum Discussion

John_Maron's avatar
John_Maron
Copper Contributor
May 12, 2023

Excel dunce! Please help! Don't even know what I'm trying to do is called!

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 nameJohn
Last nameSmith
CompanyABC Corp
Emailemail address removed for privacy reasons

 

First nameMary
Last nameJones
CompanyXYZ Corp
Emailemail 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 nameLast nameCompanyEmail
JohnSmithABC Corpemail address removed for privacy reasons
MaryJonesXYZ Corpemail 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.

excel

5 Replies

Sort By
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    May 13, 2023

    John_Maron 

    Alternative if you run Excel 2021 or 365:

    With data in A1:B8 as above:

    =INDEX(B1:B8, SEQUENCE(ROWS(B1:B8)/4, 4))
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    May 12, 2023

    John_Maron 

    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.

    • John_Maron's avatar
      John_Maron
      Copper Contributor
      May 12, 2023
      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.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        May 13, 2023

        John_Maron 

        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.

Resources