ExcelBanter - Data Sorting Technique

On Wed, 10 Oct 2012 07:18:03 +0000, swattoo wrote:

Dear All,
I have data in the following format (One Column With All the Data. Each
Row Contains Different Data as mentioned below):
See Attachments for This

360-Lifestyle (Specialized)
P.O.Box71813
Tel04-3383310
LocationNext to BMW Show Room, Sh Zayed Road, Dubai
Send Enquiry
Abdul Majeed Bicycle
P.O.Box9586
Tel02-5542517
LocationM 6, Mussafah, Abu Dhabi
Send Enquiry
Abdulla Bilal Bilal Bicycle Trading
P.O.Box23978
Tel04-3497118
LocationAl Dhiyafa Road, Satwa, Dubai
Send Enquiry

and I need this data in below mentioned format (:

360-Lifestyle (Specialized) 71813 +9714-3383310 Next to BMW Show Room,
Sh Zayed Road, Dubai Send Enquiry
Abdul Majeed Bicycle 9586 +9712-5542517 M 6, Mussafah, Abu Dhabi Send
Enquiry
Abdulla Bilal Bilal Bicycle Trading 23978 +9714-3497118 Al Dhiyafa
Road, Satwa, Dubai Send Enquiry

You can do this with formulas or with a macro.
I note that your data sets each have exactly five rows and starts in A1

So you can enter a formula like

B1: =INDEX($A$1:$A$n,(ROWS($1:1)-1)*5+INT((COLUMNS($A:A)-1/5))+1)

where the n in $A$1:$A$n represents the last row of data

Then fill right for five rows; select B1:F1 and fill down as far as needed.

This can also be done with a VBA macro, if needed.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

==============================
Option Explicit
Sub Rearrange()
'assumes data is as shown:
' starts in A1 and is contiguous
' no empty rows
' no header row
' five (5) lines per entry
'results start in C1
'columns C:G are empty
Dim vSrc As Variant, vRes() As Variant
Dim rDest As Range
Dim i As Long, j As Long, k As Long

vSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))

ReDim vRes(1 To UBound(vSrc) / 5, 1 To 5)
Set rDest = Range("C1").Resize(rowsize:=UBound(vRes, 1), _
columnsize:=UBound(vRes, 2))
rDest.EntireColumn.Clear

For i = 1 To UBound(vSrc)
j = Int((i - 1) / 5) + 1
k = (i - 1) Mod 5 + 1
vRes(j, k) = vSrc(i, 1)
Next i

rDest = vRes

End Sub
===================================