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.

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

On Jun 7, 5:15 pm, "Mike G" wrote:
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.

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





"vezerid" wrote in message
ups.com...
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

On Jun 7, 5:15 pm, "Mike G" wrote:
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.

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

--
Regards

Roger Govier


"Mike G" wrote in message
...
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.

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

"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier


"Mike G" wrote in message
...
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.

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

On Thu, 7 Jun 2007 10:15:38 -0400, "Mike G" wrote:

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.

Tx Gord.....Appreciate your effort.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 7 Jun 2007 10:15:38 -0400, "Mike G" wrote:

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.