Moving Any Text or Any Number

[spyldbrat]

In column A of my spreadsheet on row 1, I have a clients name, on row 2 I have the client ID # and row 3 and 4 I have dates associated with that particular client. Row 5 contains another clients name, row 6 is the client ID # and row's 7 to 14 contain dates and so...The number of dates associated with each client will vary.

I need to move the clients name and client ID# to columns B & C - NEXT to the dates for each client. This would also involve inserting a column so that the data in column C shifts one to the right.

This is what it currently looks like:
A
1 CLIENT NAME 1
2 CLIENT ID 1
3 DATE (Client 1)
4 DATE (Client 1)
5 CLIENT NAME 2
6 CLIENT ID 2
7 DATE (Client 2)
8 DATE (Client 2)
9 DATE (Client 2)
10 DATE (Client 2)
11 DATE (Client 2)
12 DATE (Client 2)
13 DATE (Client 2)

This is what I need it to look like:
A B C

1 DATE (Client 1) CLIENT NAME 1 CLIENT ID 1
2 DATE (Client 1) CLIENT NAME 1 CLIENT ID 1
3
4 DATE (Client 2) CLIENT NAME 2 CLIENT ID 2
5 DATE (Client 2) CLIENT NAME 2 CLIENT ID 2
6 DATE (Client 2) CLIENT NAME 2 CLIENT ID 2
7 DATE (Client 2) CLIENT NAME 2 CLIENT ID 2
8 DATE (Client 2) CLIENT NAME 2 CLIENT ID 2
9 DATE (Client 2) CLIENT NAME 2 CLIENT ID 2
10 DATE (Client 2) CLIENT NAME 2 CLIENT ID 2

 

[Joe4]

Are the Client IDs alphanumeric or numeric?

Would you mind posting a small sample of what the data actually looks like?

Also, what row does that data start on (is there a header row)?

 

[Joe4]

Depending on the formats of your data, this code may work:

Sub MyMacro()

    Dim myRow As Long
    Dim lastRowDate As Boolean
    Dim myClient As String
    Dim myID As String
    
    Application.ScreenUpdating = False
    
'   Set initial values
    lastRowDate = True
    myRow = 1
    
'   Loop through rows
    Do
'       See if we start a new client
        If lastRowDate And Not (IsDate(Cells(myRow, "A"))) Then
'           Capture client
            myClient = Cells(myRow, "A")
'           Clear current row
            Cells(myRow, "A").ClearContents
'           Set lastRowDate
            lastRowDate = False
'           Go to next row
            myRow = myRow + 1
        Else
'           What to do if second row
            If (lastRowDate = False) And Not (IsDate(Cells(myRow, "A"))) Then
'               Capture ID
                myID = Cells(myRow, "A")
'               Set lastRowDate
                lastRowDate = False
'               Delete current row
                Rows(myRow).Delete
            Else
'               Handle date row
                If IsDate(Cells(myRow, "A")) Then
'                   Assign values
                    Cells(myRow, "B") = myClient
                    Cells(myRow, "C") = myID
'                   Set lastRowDate
                    lastRowDate = True
'                   Go to next row
                    myRow = myRow + 1
                End If
            End If
        End If
    Loop Until Cells(myRow, "A") = ""
    
    Application.ScreenUpdating = True

End Sub

 

[spyldbrat]

Thank you. The Macro works perfectly!

 

[Joe4]

You are welcome!