Consulting

Results 1 to 6 of 6

Thread: Reorganizing data

  1. #1

    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!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

  4. #4
    this is a duplcate to a post in another forum

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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    That's enough to turn me off, but can you say where in case anyone else is interested in following up.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •