Change column of data to rows

I have a column of data that is mixed up. I have tried to sort at source but this is taking some time and I hoped there maybe is a faster way in excel to do this.

There is currently 1 column of data, everytime i come across a cell that either starts TXF or XF then I want all the records in each cell below to be transposed into a row until it comes to the next TXF or XF id number.

can anyone help with VBA on this?
bryanscott53Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
andrewssd3Connect With a Mentor Commented:
Try this - it assumes you have the raw data sheet active when you start, and it creates a new sheet for the results:
Public Sub ReformatData()

    Dim wksOut As Excel.Worksheet
    Dim c As Excel.Range
    Dim rngIn As Excel.Range
    Dim rngOut As Excel.Range
    Dim rngCopy As Excel.Range
    
    Set rngIn = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(1).EntireColumn)
    
    Set wksOut = ActiveWorkbook.Worksheets.Add()
    wksOut.Activate
    Set rngOut = wksOut.Cells(1)
    
    For Each c In rngIn
        If (c.Value Like "TXF*") Or (c.Value Like "XF") Then
            ' if we have a previous range, copy it out first
            If Not (rngCopy Is Nothing) Then
                rngCopy.Copy
                rngOut.Select
                Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, _
                    Transpose:=True
                ' move the output range on
                Set rngOut = rngOut.Offset(1, 0)
            End If
            Set rngCopy = c.Cells(1)
        Else
            ' rngcopy won't be nothing unless there were stray items before the first start marker
            If Not rngCopy Is Nothing Then
                ' extend the range to copy
                Set rngCopy = rngCopy.Resize(rngCopy.Rows.Count + 1, 1)
            End If
        End If
        
    Next c
    
    ' copy the final range if applicable
    If Not (rngCopy Is Nothing) Then
        rngCopy.Copy
        rngOut.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, _
            Transpose:=True
     End If
    
    MsgBox "Complete"
    
End Sub

Open in new window

0
 
StephenJRCommented:
Perhaps you could post a sample workbook.
0
 
David_HagermanCommented:
I take it that you have selected the Cells you want to sort and Clicked Sort on the Editing toolbar at the top of your screen. If you can't find it let me know what version of Office you are running
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
bryanscott53Author Commented:
David, I dont need to sort, sorry maybe my explanation is not clear. I have posted a sample book, there are roughly about 25,000 entries down column a in the actual workbook. I have 2 sample sheets in this one to show what I am trying to achieve.
testdata1.xlsx
0
 
andrewssd3Commented:
Sorry missed an asterisk from line 16:
        If (c.Value Like "TXF*") Or (c.Value Like "XF*") Then

Open in new window

0
 
bryanscott53Author Commented:
perfect, thank you so much!
0
 
David_HagermanCommented:
Andrewssd3 - Wanted to keep this for my records but i get an error on line 3 Char 16 - Expected end of statement.

Do you know what could be causing this? I tried to run the script on the file that Bryan sent earlier?
0
 
andrewssd3Commented:
David - sounds like you may be trying to run it as a VBScript, so the error is because VBScript does not support 'As' to define variable types.  The code was written for VBA - if you paste it into an Excel module in the VBA editor it should work fine.  If you wanted to change it into a VBScript, you'd need to add some extra code to instantiate an Excel application first, then do the normal things we have to do to convert VBA to VBScript - remove all 'As' clauses, change Next statements, etc.
0
 
David_HagermanCommented:
I was trying to run it as a VBS, Thank you will try as VBA

Thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.