?
Solved

Change column of data to rows

Posted on 2011-09-26
9
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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.

764 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