transposing addresses

[Mike G]

Greetings...I have a list of addresses in a1:c20 in the format a1=name
b1=street c1=city state and I want to transpose them into a vertical
alignment with a possible empty row between them. Used formula
=transpose(a1:c20), did f2 and ctrl+shift+enter and result is value#. any
help appreciated.

 

[vezerid]

Let us first ensure you performed the procedure correctly:
A1:C20 is a 20x3 matrix. Hence its transpose will be a 3x20 matrix.

Select a 3-row by 20-column area. In the formula bar enter

=TRANSPOSE(A1:C20)

Commit with Shift+Ctrl+Enter.

Does this work?
Kostis Vezerides

 

[Mike G]

Thank you....yes that works, however I do not get the results I wanted. My
question was not clear, and perhaps transpose is not the correct function
I want the result to be in the format:
name
address
city, state

sorry for the mixup

 

[Roger Govier]

Hi Mike

Try entering in E1
=IF(MOD(ROW(),4)=0,"",INDEX($A:$D,INT(ROW()/4)+1,ROW()-(INT(ROW()/4))*4))
and copy down

 

[Mike G]

Roger...that works great. I then can COPY|PASTE SPECIAL|VALUES and I am in
business. Much appreciated.!!

 

[Gord Dibben]

Mike

Can you live with a macro which will copy the addresses to a new sheet in the
format you wish?

Sub rowstocol()

Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = InputBox("Enter Number of Columns to Transpose to Rows")

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.EntireRow.Insert
wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End Sub


Gord Dibben MS Excel MVP

 

[Mike G]

Tx Gord.....Appreciate your effort.

Mike

Can you live with a macro which will copy the addresses to a new sheet in
the
format you wish?

Sub rowstocol()

Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = InputBox("Enter Number of Columns to Transpose to Rows")

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count,
ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.EntireRow.Insert
wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End Sub


Gord Dibben MS Excel MVP