Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

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

5 Replies

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

get and transform.JPG

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.

@John_Maron 

Alternative if you run Excel 2021 or 365:

Sample.png

With data in A1:B8 as above:

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

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

transform.JPG