Solved

Change column of data to rows

Posted on 2011-09-26
9
216 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create excel pivot chart 12 43
Excel Drop Down List 13 34
EXCEL formual to calculate Quarter 6 37
vba delete dups is not working 35 15
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

810 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