Reorganizing data

[eb818]

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!

[Bob Phillips]

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

[eb818]

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.

[westconn1]

this is a duplcate to a post in another forum

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

[Bob Phillips]

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

[westconn1]

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