Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Change column of data to rows

Posted on 2011-09-26
9
Medium Priority
?
254 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

569 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