Transposing ~ 25,000 rows of data into Columns

[strickrn]

I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet.

Row Labels Data in Rows
Full Name Nick Strickland
Job Title Vice President
Company SC Products International
Business Street 11 Airport Road
Business City, State Redding, DE 96002-9217
Business Phone 800-555-4545
Business Fax 800-555-1234
Web Page www.vicepresident.com
E-Mail Address nick@vicepresident.com
Full Name Harry Parker
Company Supply Industries
Business Street 2166 South Flagler Ave.
Business City, State Yuma, AZ 85365-2420
Business Phone 800-555-4545
Business Fax 800-555-1234
Web Page www.peaceout.com
Job Title Gen. Mgr.
Company Handrew company
Business Street 40 Brookside Court
Business Street P. O. Box 12444
Business City, State Nader, WI 45668
Business Phone 800-555-4545
Business Fax 800-555-1234
Web Page www.hangtime.com
E-Mail Address dachtemeier@nbhandy.com

[teylyn]

Hi strickrn,

welcome to the forum.

Is there an oversight in your data? I see only two names and the second name has two sets of addresses??

[strickrn]

This is a sample dataset to upload to the forum. I can copy and transpose for a small amount of data but would like a more elegant solution for the 25,000 rows or so of data that I'd like to convert.

[teylyn]

I understand your requirements. I was just wondering if the data sample you supplied contains errors, or if it is exemplary of the data you've got. We need to nail which row label serves as the indicator for a new record and if the Full Name may be missing, this must be taken into account.

[strickrn]

Ah....OK. The Row label "Company" can be used as the new row indicator. This is fun!

[teylyn]

So, if the company name is the trigger for a new record, does that mean that Harry Parker works for SC Products International?

This does not make sense.

Again: does your data sample contain errors, i.e. is there a Full Name missing betewen

Web Page www.peaceout.com

and

Job Title Gen. Mgr.

???

[strickrn]

Yes. Data is missing randomly throughout the dataset. For example, the Full Name title may be missing for one company, Title may be missing for another, Email may be missing for another, so there is no repeatable consistent format to the column of data.

[teylyn]

so, in terms of human logic, how would you determine which row label flags the new record if any of them can be missing? Can it be assumed that

- the row labels are always in the same order,
- this is also the order of the columns you have specified
- if a row label matches a column label, and that column label has already been filled in for the current record, then that means we're dealing with the next record?

Please confirm or correct the logic.

[strickrn]

Yes, the row labels are always in the same order
Yes, this is also the order of the columns you have specified
Yes, if a row label matches a column label, and that column label has already been filled in for the current record, then that means we're dealing with the next record?

[teylyn]

OK VBA experts, I've done all the spec work, now it's up to you for the coding ...

[D_Rennie]

Here is a quick shot at the code.

Sub DoTheFunkeyStuff()
Dim x As Long
Dim CL As Range
With Sheets("sheet1")
    For Each CL In Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        If CL.Value = "Business Street" And CL.Offset(-1, 0).Value = "Business Street" Then
            .Range("G" & (.Cells(.Rows.Count, "F").End(xlUp).Row)).Value = CL.Offset(, 1).Value
            GoTo Bail
        End If
            Select Case CL.Value
                Case "Full Name"
                .Range("C" & (.Cells(.Rows.Count, "C").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Job Title"
                .Range("D" & (.Cells(.Rows.Count, "D").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Company"
                .Range("E" & (.Cells(.Rows.Count, "E").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Business Street"
                .Range("F" & (.Cells(.Rows.Count, "F").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Business City, State"
                .Range("H" & (.Cells(.Rows.Count, "H").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Business Phone"
                .Range("I" & (.Cells(.Rows.Count, "I").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Business Fax"
                .Range("J" & (.Cells(.Rows.Count, "J").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Web Page"
                .Range("K" & (.Cells(.Rows.Count, "K").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "E-Mail Address"
                .Range("L" & (.Cells(.Rows.Count, "L").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case Else
                MsgBox "Barney Rubble"
            End Select
Bail:
    Next CL
.Columns.AutoFit
End With
End Sub

there is a added column to deal with post office address. It look ok too me though i didnt look to close

[D_Rennie]

i think this cracks it

Sub DoTheFunkeyStuff()
Dim x As Long
Dim CL As Range
With Sheets("sheet1")
    For Each CL In Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        If CL.Value = "Business Street" And CL.Offset(-1, 0).Value = "Business Street" Then
            .Range("G" & (.Cells(.Rows.Count, "F").End(xlUp).Row)).Value = CL.Offset(, 1).Value
            GoTo Bail
        End If
            Select Case CL.Value
                Case "Full Name"
                .Range("C" & (.Cells(.Rows.Count, "E").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Job Title"
                .Range("D" & (.Cells(.Rows.Count, "E").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Company"
                .Range("E" & (.Cells(.Rows.Count, "E").End(xlUp).Row + 1)).Value = CL.Offset(, 1).Value
                Case "Business Street"
                .Range("F" & (.Cells(.Rows.Count, "E").End(xlUp).Row)).Value = CL.Offset(, 1).Value
                Case "Business City, State"
                .Range("H" & (.Cells(.Rows.Count, "E").End(xlUp).Row)).Value = CL.Offset(, 1).Value
                Case "Business Phone"
                .Range("I" & (.Cells(.Rows.Count, "E").End(xlUp).Row)).Value = CL.Offset(, 1).Value
                Case "Business Fax"
                .Range("J" & (.Cells(.Rows.Count, "E").End(xlUp).Row)).Value = CL.Offset(, 1).Value
                Case "Web Page"
                .Range("K" & (.Cells(.Rows.Count, "E").End(xlUp).Row)).Value = CL.Offset(, 1).Value
                Case "E-Mail Address"
                .Range("L" & (.Cells(.Rows.Count, "E").End(xlUp).Row)).Value = CL.Offset(, 1).Value
                Case Else
                MsgBox "Barney Rubble"
            End Select
Bail:
    Next CL
.Columns.AutoFit
End With
End Sub

probley not the most elegent way. Though as long as it works right.

cheers

[strickrn]

Good morning. Thanks so much for your brilliance!!!! I'll give it a try and let you know what happens....

[strickrn]

D_Rennie - I installed the code and it worked perfectly. It filled in up to 2996 rows of data. I'm really dumb when it comes to macro writing, so is there a way to get it to transpose up to about 30,000 rows of data.

[strickrn]

DUH !!!!! OF COURSE IT STOPPED AT 2996 ROWS....That's how many records are in the dataset.....THIS IS BRILLIANT WORK....

THANK YOU SO VERY MUCH.....

[moniquem]

I cant see the attached codes.

[moniquem]

Hi! how about if I want to transpose many columns of data into rows and in different sheets?

here is an example. But in reality, it is composed of different sheets (1 sheet per variable).

I want the format to be like this:

DATE (month/day/yr) Variable 1 Variable 2 Variable 3

jan 1, 1999 3 4 5

up to

jan 31, 1999 6 7 8



The raw data contains many years. I wish to have the format like the above one. 1 year per sheet.

[D_Rennie]

no probs just double cheek everythink is going where it should be.

[strickrn]

I worked PERFECT!!! Thanks so much. How do I label this thread as [SOLVED]?

[teylyn]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[arlu1201]

What do you mean? Are you getting any error? Which post are you talking about?

[jeffreybrown]

It seems Arlette was asking a question...

What do you mean? Are you getting any error? Which post are you talking about?

...but rather you posted a question.

As per the forum rules, please start your own thread...