COLUMN WITH UNEQUAL BLOCKS TO TABLE

[FARAZ QURESHI]

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: (e-mail address removed)
Name: 2
Department: 2
Cell: yyy
Res: xxx
Email: (e-mail address removed)
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: (e-mail address removed) Res: xxx Email: (e-mail address removed)

Your help, a fortmula or a code and guidance shall highly obliged!

 

[FARAZ QURESHI]

Sorry,

The desired table is not being shown correctly I mean:

Name: 1 Name: 2 Name: 3
Department: 1 Department: 2 Department: 3
Cell: xxx Cell: xxx Cell: xxx
Email: (e-mail address removed) Res: xxx
Email: (e-mail address removed)

i.e. 4, 5 and 3 entries in columns "A", "B" & "C" respectively.

 

[Chip Pearson]

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)

 

[FARAZ QURESHI]

U R simply the best CHIP!!!!!!!!!!!!!!!

Thanx!!!!

Yours ever-admiring fan!