Excel 2007 Macro to Take Row of Text and Place in Single Column

Hello -

Hoping someone can assist in developing a code that will essentially un-pivot text in rows.  Difficult to explain but when you see the attached sample it will hopefully make more sense.  The purpose of this is to generate a word cloud graphic in different software but in order to accomplish it I need the text in each row to line up in a single column notated by identifiers in columns A & B.

Again, best way to get across what I'd like the code to do is to look at the sample.  The 'Raw Data' worksheet shows how it appears once the extract takes place and the 'Should Look After Macro' is how the data should look after the code is ran.

Any support would be greatly appreciated!
EE-Sample.xlsx
LVL 1
EscanabaManager - HR AnalyticsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Try this...

Sub TabulateData()

    Dim SourceSheet As Worksheet
    Dim DestSheet As Worksheet
    Dim rw As Long
    Dim col As Long
    
    
    Set SourceSheet = Worksheets("Raw Data")
    Set DestSheet = Worksheets("Sheet1")
    'use this to create a new sheet if not already present
    'Set DestSheet = Worksheets.Add
    
    'insert columns headers in destination sheet
    DestSheet.Range("A1:C1").Value = SourceSheet.Range("A1:C1").Value
    
    For rw = 2 To SourceSheet.Cells(Rows.Count, "A").End(xlUp).Row
        For col = 3 To SourceSheet.Cells(rw, Columns.Count).End(xlToLeft).Column
            With DestSheet.Cells(Rows.Count, "A").End(xlUp)
                .Offset(1, 0) = SourceSheet.Cells(rw, "A")
                .Offset(1, 1) = SourceSheet.Cells(rw, "B")
                .Offset(1, 2) = SourceSheet.Cells(rw, col)
            End With
        Next
    Next
    
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EscanabaManager - HR AnalyticsAuthor Commented:
Works great!  Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.