Solved

Change column of data to rows

Posted on 2011-09-26
9
217 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:bryanscott53
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36598415
Perhaps you could post a sample workbook.
0
 
LVL 7

Expert Comment

by:David_Hagerman
ID: 36598454
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
 

Author Comment

by:bryanscott53
ID: 36598571
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36598844
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36598852
Sorry missed an asterisk from line 16:
        If (c.Value Like "TXF*") Or (c.Value Like "XF*") Then

Open in new window

0
 

Author Closing Comment

by:bryanscott53
ID: 36598955
perfect, thank you so much!
0
 
LVL 7

Expert Comment

by:David_Hagerman
ID: 36599008
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36599083
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
 
LVL 7

Expert Comment

by:David_Hagerman
ID: 36599096
I was trying to run it as a VBS, Thank you will try as VBA

Thank you for your help
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question