Excel macro needed

[champaneria]

I have large Excel table, many rows data in one column, representing data fields, repeated for each data record. e.g.: like so:
DATA
john
town1
22345
stan
town2
45690
edith
town3
56129

Many such rows. The same pattern repeats. I do not have the field name and record numbers in the form;
field record no DATA
a 1 john
b 1 town1
c 1 22345
a 2 stan
b 2 town2
c 2 45690
a 3 edith
b 3 town3
c 3 56129


I need a macro to convert such data into columns each representing data fields.
The result desired:
record no/field> a b c
1 john town1 22345
2 stan town2 45690
3 edith town3 56129

Pl. note that there may be hundreds of records and many data fields.
Need a macro where record number is 1 to i, and data field, 1 to j.

[anilsolipuram]

For the code I used a1:a21 as the range of rows having the defined pattern, I wrote the macro to copy the pattern to separate columns ( the result will be stored in adjacent columns).


go through the columns you should be able to comprehend



Sub Macro1()
Dim r As Range
Set r = Range("a1:a21") 'range of the column where the same pattern repeats.
Dim k, in_adr As Variant
k = 0
For Each c In r
If k = 0 Then
in_adr = c.Address
Range(in_adr).Select
End If
Range(ActiveCell.Address, ActiveCell.Offset(2, 0).Address).Select 'selecting three rows at a time
Selection.Copy 'copy the selection
Range(in_adr).Offset(k, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True 'paste the selection to adjacent column
k = k + 1
Range(in_adr).Offset(k * 3, 0).Select 'offset the selection by 3
If k >= r.Count / 3 Then
End
End If
Next
End Sub