Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COLUMN WITH UNEQUAL BLOCKS TO TABLE
Dear Fellows,
The problem is that I commonly have a very large list imported from .txt to ..xls or .xlsx which has the blocks not of common/equal size. An example is Column "A" showing: Name: 1 Department: 1 Cell: xxx Email: Name: 2 Department: 2 Cell: yyy Res: xxx Email: Name: 3 Department: 3 Cell: xxx However, note that every new block starts with "Name" as the first 4 characters. Any idea how to convert such a data to: Name: 1 Name: 2 Name: 3 Department: 1 Department: 2 Department: 3 Cell: xxx Cell: xxx Cell: xxx Email: Res: xxx Email: Your help, a fortmula or a code and guidance shall highly obliged! -- Best Regards, FARAZ A. QURESHI |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COLUMN WITH UNEQUAL BLOCKS TO TABLE
Try code like the following. Change the lines marked with "<<<" to the
appropriate values: Sub VarColToTable() Dim R As Range Dim Dest As Range Dim DR As Range Dim LastRow As Long Dim SourceWS As Worksheet Dim DestinationWS As Worksheet Set SourceWS = Worksheets("Sheet1") '<<< CHANGE Set DestinationWS = Worksheets("Sheet2") '<<< CHANGE Set R = SourceWS.Range("A1") '<<< CHANGE With SourceWS LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row End With Do Until R.Row LastRow If UCase(R.Text) Like "NAME*" Then If Dest Is Nothing Then Set Dest = DestinationWS.Range("E1") '<<< CHANGE Else Set Dest = Dest(1, 2) End If Set DR = Dest End If DR.Value = R.Text Set DR = DR(2, 1) Set R = R(2, 1) Loop MsgBox "Data split on '" & DestinationWS.Name & "'." End Sub Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 17 Dec 2008 04:44:21 -0800, FARAZ QURESHI wrote: Dear Fellows, The problem is that I commonly have a very large list imported from .txt to .xls or .xlsx which has the blocks not of common/equal size. An example is Column "A" showing: Name: 1 Department: 1 Cell: xxx Email: Name: 2 Department: 2 Cell: yyy Res: xxx Email: Name: 3 Department: 3 Cell: xxx However, note that every new block starts with "Name" as the first 4 characters. Any idea how to convert such a data to: Name: 1 Name: 2 Name: 3 Department: 1 Department: 2 Department: 3 Cell: xxx Cell: xxx Cell: xxx Email: Res: xxx Email: Your help, a fortmula or a code and guidance shall highly obliged! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COLUMN WITH UNEQUAL BLOCKS TO TABLE
U R simply the best CHIP!!!!!!!!!!!!!!!
Thanx!!!! Yours ever-admiring fan! -- Best Regards, FARAZ A. QURESHI "Chip Pearson" wrote: Try code like the following. Change the lines marked with "<<<" to the appropriate values: Sub VarColToTable() Dim R As Range Dim Dest As Range Dim DR As Range Dim LastRow As Long Dim SourceWS As Worksheet Dim DestinationWS As Worksheet Set SourceWS = Worksheets("Sheet1") '<<< CHANGE Set DestinationWS = Worksheets("Sheet2") '<<< CHANGE Set R = SourceWS.Range("A1") '<<< CHANGE With SourceWS LastRow = .Cells(.Rows.Count, R.Column).End(xlUp).Row End With Do Until R.Row LastRow If UCase(R.Text) Like "NAME*" Then If Dest Is Nothing Then Set Dest = DestinationWS.Range("E1") '<<< CHANGE Else Set Dest = Dest(1, 2) End If Set DR = Dest End If DR.Value = R.Text Set DR = DR(2, 1) Set R = R(2, 1) Loop MsgBox "Data split on '" & DestinationWS.Name & "'." End Sub Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 17 Dec 2008 04:44:21 -0800, FARAZ QURESHI wrote: Dear Fellows, The problem is that I commonly have a very large list imported from .txt to .xls or .xlsx which has the blocks not of common/equal size. An example is Column "A" showing: Name: 1 Department: 1 Cell: xxx Email: Name: 2 Department: 2 Cell: yyy Res: xxx Email: Name: 3 Department: 3 Cell: xxx However, note that every new block starts with "Name" as the first 4 characters. Any idea how to convert such a data to: Name: 1 Name: 2 Name: 3 Department: 1 Department: 2 Department: 3 Cell: xxx Cell: xxx Cell: xxx Email: Res: xxx Email: Your help, a fortmula or a code and guidance shall highly obliged! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is results table mean for-t-TestTwoSample unequal variance | Excel Discussion (Misc queries) | |||
Formula Bar blocks column header letters so I can't paste | New Users to Excel | |||
Multiple blocks (double column lists) turned into drop down list | Excel Discussion (Misc queries) | |||
lookup and choose wih multiple tables of unequal column lengths | Excel Discussion (Misc queries) | |||
unequal sign | Excel Discussion (Misc queries) |