Reorganizing data

Hello!

Thank you for taking the time to read this. I am not the biggest technology person but the company I work for is trying to find a way to update its database of clients. I'm sure you'll probably be able to tell that I am not the best at this stuff by the way I describe my issue.

I am trying to organize a bunch of data in Excel from a website.When I copy and paste, it comes out like this:

Name: John Smith, PGA
City/State: Scottsdale, AZ
Classification: A-1
Club: Desert Mountain Golf Club
Section: Southwest Section
Phone: (867) 555-5555
E-Mail: jjjjjjjjj
Name: Dale R Johnson, PGA
City/State: Scottsdale, AZ
Classification: A-14
Club: Desert Mountain/Renegade
Section: Southwest Section
Phone: (444) 555-6666
E-Mail: ddddd
Name: Jack A Adams
City/State: Maricopa, AZ
Classification: B-8
Club: Southern Dunes Golf Club
Section: Southwest Section
Phone: (777) 888-9999
E-Mail: sssss

The data pastes just fine and actually is helpful that it splits the categories and actual information up by cell. In other words, the Email is in a separate cell from the actual email itself.

What I am trying to do is make it so that way the information appears like this:

Name: City/State Classification
John Smith Scottsdale, AZ A-1
Dale R Johnson Scottsdale, AZ A-14
Jack A Adams Maricopa, AZ B-8

I think you get the idea. Everything would be in their own cell. I don't know if this is something that would even be possible through coding, but if it is, would anyone be willing to show me how you would do it? I have tried to teach myself coding online, but I just don't seem to get it. I realize that nobody on this forum has to, nor am I expecting anyone to, but it would be a huge help if you could and I would greatly appreciate it!

Code:

---

Public Sub Reorganize()
Dim lastrow As Long
Dim i As Long
   
    Application.ScreenUpdating = True

    With ActiveSheet
   
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = lastrow - 6 To 1 Step -7
       
            .Cells(i, "A").Value = Mid$(.Cells(i, "A"), InStr(.Cells(i, "A"), ":") + 2, 99) & " " & _
                                  Mid$(.Cells(i + 1, "A"), InStr(.Cells(i + 1, "A"), ":") + 2, 99) & " " & _
                                  Mid$(.Cells(i + 2, "A"), InStr(.Cells(i + 2, "A"), ":") + 2, 99)
            .Rows(i + 1).Resize(6).Delete
        Next i
        .Rows(1).Insert
        .Range("A1").Value = "Name City/State Classification"
    End With
   
    Application.ScreenUpdating = False
End Sub

---

Thank you for your response! Unfortunately, this code does not quite achieve what I am looking for. When I implement the code, my data comes out like this:

Name City/State Classification (These three came out in one cell (A1). I was hoping for all of the categories to end up at the top so Name would be in A1, City/State B1, Classification C1, Club D1, etc.

The names of the individuals came out correctly stacked on each other but they came out in the B column.

So it looked like this:
John Smith
Dale R Johnson
Jack A Adams

This is what I am pretty much looking for with all of my data. With the names, I would want them under the Name column in the A column. Then the City/State that they are in next to the name in the B column, and so on and so forth with the rest of the data in each of the rest of the columns.

this is a duplcate to a post in another forum

though it does specify there that it is to run on Mac

That's enough to turn me off, but can you say where in case anyone else is interested in following up.

vbforums
office development forum

i have looked at it there, but as i do not own a Mac, not sure if i could help at all anyway