Help with moving a multi-row record

  • Thread starter Thread starter Andrew H
  • Start date Start date
A

Andrew H

Hi,
I have a singel excel column that has names addresses and
phone numbers. I need to move the records to a single row
each.

name
address
phone

needs to be
name, address, phone numbers.

can this be done in a macro? can you help.. Ihave 4000
records to move to a csv file from the original format.

Thanks...
 
Andrew

There are a couple of ways of doing this.

1) You could write a macro.
2) If you want to do this without a macro,then
a) Assume that the data is in column A
b) Assume that there is a blank line between each block
In C1 - E1 put in the headings (Name, Address, Phone)
In C2 put in the formula
=INDIRECT("A"&ROW()*4-(10-COLUMN()))
and copy across to E2. This can then be copied down to
cover all the source data.

If there is no blank row between each entry, then the
formula is
=INDIRECT("A"&ROW()*3-(8-COLUMN()))

Tony
 
Andrew

Try this macro.

''routine to take 1 column of a variable number of rows(including blanks) and
put in a choice of number of columns with A1,A2,A3,etc.moved to A1,B1,C1,etc.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
''Dim nocols As Integer
goagain:
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then GoTo tryover
For i = 1 To rng.row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.row, "A")).ClearContents
Exit Sub
tryover:
Style = vbYesNo
msg = "You Have Cancelled " & Chr(13) _
& "Or Not Entered Criteria" & Chr(13) _
& "Do You Wish To Try Again?"
response = MsgBox(msg, Style)
Set srng = Nothing
If response = vbYes Then GoTo goagain
If response = vbNo Then Exit Sub
On Error GoTo 0
End Sub

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
Back
Top