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?
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?
StephenJR
Perhaps you could post a sample workbook.
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
ASKER
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
testdata1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry missed an asterisk from line 16:
If (c.Value Like "TXF*") Or (c.Value Like "XF*") ThenASKER
perfect, thank you so much!
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?
Do you know what could be causing this? I tried to run the script on the file that Bryan sent earlier?
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.
I was trying to run it as a VBS, Thank you will try as VBA
Thank you for your help
Thank you for your help