redistribute a column by a groups of rows

[Bill Davis]

Existing Conditions:
Single Excel Sheet
1 Column of data
~3000 rows of data
Data is grouped in clusters of information seperated by blank rows (address
information)
Data groupings can be anywhere from 4 to 10 rows

Name
Address 1
Address 2
Address 3
City
State

Name
Address 1
City
State

etc to ~3000 rows of 483 discreet groups

I want to change so that it displays (; represent columns)
Name;address1;address2;address3;city;state
Name;Address1;city;state
etc
Any help?

 

[Per Erik Midtrød]

I think this does what you are looking for:

Sub Redistribute()
Dim r, s, t As Long
s = 2
t = 1
For r = 1 To 3000
If Cells(r, 1).Value <> "" Then
Cells(t, s).Value = Cells(r, 1).Value

End If

s = s + 1
If Cells(r, 1).Value = "" Then
s = 2
t = t + 1
End If
Next

End Sub

Your excisting data has to be in column A.

Per Erik