Formatting Data in Excel using VBA

[Growler]

Hello,

I'm trying to take large amounts of data in a single column over many rows and sift out the data I need into labeled columns on a single rows.

I've taken screen shots of what I'm trying to accomplish.

Code:
'Store column headings in an array
ColumnHeading = Array("", "", "", "College", "Program", "Phone", _
"Email", "Web Site", "Certificate", "Associate", "Baccalaureate")

'Format Column Headings with values from array
For ColIndex = 4 To UBound(ColumnHeading)
Cells(1, ColIndex).Value = ColumnHeading(ColIndex)
Next ColIndex

'Scan through Column A, if column contains "E-mail Address", copy e-mail address into "E-mail address Column
Dim CopiedValue As Variant
For RowIndex = 3 To totalRows
Const firstColumn As Integer = 1
If Cells(RowIndex, firstColumn).Value Like "*University*" Then
'Cells(RowIndex, 4).Value = Cells(RowIndex, firstColumn).Value
Cells(RowIndex, 4).Value = Cells(RowIndex, firstColumn).Value

ElseIf Cells(RowIndex, firstColumn).Value Like "*Dental Hygiene *" Then
Cells(RowIndex, 5).Value = Cells(RowIndex, firstColumn).Value

ElseIf Cells(RowIndex, firstColumn).Value Like "*Phone: * " Then
Cells(RowIndex, 6).Value = Cells(RowIndex, firstColumn).Value

ElseIf Cells(RowIndex, firstColumn).Value Like "*Email Address: * " Then
Cells(RowIndex, 7).Value = Cells(RowIndex, firstColumn).Value

ElseIf Cells(RowIndex, firstColumn).Value Like "*Web Site: *" Then
Cells(RowIndex, 8).Value = Cells(RowIndex, firstColumn).Value

ElseIf Cells(RowIndex, firstColumn).Value Like "*Certificate: *" Then
Cells(RowIndex, 9).Value = Cells(RowIndex, firstColumn).Value

ElseIf Cells(RowIndex, firstColumn).Value Like "*Associate: *" Then
Cells(RowIndex, 10).Value = Cells(RowIndex, firstColumn).Value

ElseIf Cells(RowIndex, firstColumn).Value Like "*Baccalaureate: *" Then
Cells(RowIndex, 11).Value = Cells(RowIndex, firstColumn).Value
End If
Next RowIndex

1) Before I run the code, I have large amounts of data in a single column:

2) The code scans through column 1 looking for data that matches column types, then places them in the correct columns:

3) This is what it SHOULD be doing... each time the code reaches a new college, it should place it in the next row

Any suggestions? thanks!

 

[Growler]

Here is a revised version which adds specific information for each search for the mid function. Mid works similar as in excel MID(STRING,START,NUM_OF_CHAR)

Sub transposedata()

newrow = 1

Columns("D:K").ClearContents

columnHeading = Array(, , , , "College", "Program", "Phone", _
"Email", "Web Site", "Certificate", "Associate", "Baccalaureate")

For j = 4 To 11
    Cells(1, j) = columnHeading(j)
Next j

For i = 2 To [a65000].End(xlUp).Row

    
        If InStr(Cells(i, 1), "University") = 1 Then
            newrow = newrow + 1
            col = 4
            trigger = 1
            st = 1
            ElseIf InStr(Cells(i, 1), "Dental Hygiene ") = 1 Then
                col = 5
                trigger = 1
                st = 1
            ElseIf InStr(Cells(i, 1), "Phone: ") = 1 Then
                col = 6
                trigger = 1
                st = 8
            ElseIf InStr(Cells(i, 1), "Email Address: ") = 1 Then
                col = 7
                trigger = 1
                st = 16
            ElseIf InStr(Cells(i, 1), "Web Site: ") = 1 Then
                col = 8
                trigger = 1
                st = 11
            ElseIf InStr(Cells(i, 1), "Certificate: ") = 1 Then
                col = 9
                trigger = 1
                st = 14
            ElseIf InStr(Cells(i, 1), "Associate: ") = 1 Then
                col = 10
                trigger = 1
                st = 11
            ElseIf InStr(Cells(i, 1), "Baccalaureate: ") = 1 Then
                col = 11
                trigger = 1
                st = 16
            Else
                trigger = 0
        End If
    
    
    If trigger = 1 Then
    
        Cells(newrow, col) = Mid(Cells(i, 1), st, Len(Cells(i, 1)) - st + 1)
        
    End If

Next i

End Sub

I'm sorry, can you please explain what "st" does, and why you've chosen the values you have for each st in each if statement?

thanks