Transpose

  • Thread starter Thread starter JG
  • Start date Start date
J

JG

I have worksheet like this:

A
B
C
A
B
C

That I need to look like this:

ABC
ABC

In both, each letter is in its own cell.

Is there one forumula that I can use to do all-at-once or do I need to do
each set (ABC) individually?

Thanks, JG
 
Hello there.

If I'm reading you correctly then you do want to try and transpose
your information by selecting it, cut or copy it, then select where
you want it to go, then right click that cell and choose the paste
special option, then check on the Transpose option and VIOLA!

And then you can use your autofill handle.

Did that help?

-Imonit
 
I don't think I explained it right. The values are different in each cell.

A
1 Name
2 Address
3 Phone
4 Name
5 Address
6 Phone

A B C
Name Address Phone
Name Address Phone

I use the transpose special for the first three, but I don't know how to do
it for the rest w/o doing it three at-a-time.

Thx. JG
 
You can try a macro like this one:
Sub Transpose3Line()
'stop at the first blank row
While ActiveCell <> ""
'move contents of next 2 down over on this row
ActiveCell.Offset(0, 1).Formula = ActiveCell.Offset(1, 0).Formula
ActiveCell.Offset(0, 2).Formula = ActiveCell.Offset(2, 0).Formula
'move down one
ActiveCell.Offset(1, 0).Select
'delete two empty rows
Selection.EntireRow.Delete
Selection.EntireRow.Delete
Wend
End Sub

Note that rows are being deleted by this; if there is stuff in other
columns it will be destroyed. If you do have data in other columns you
can replace the 2 "Selection.EntireRow.Delete" lines with
"Selection.Delete Shift:=xlUp" instead.
 
I have a similar problem.

But my list looks like this

name
address
city
phone

name
address
city

name
address
city
email
Phone

I would like the data to look like this

name address city phone
name address city
name address city email Phone

Do you know if it can be done with a macro? I have tried recording one but
have had no success.

Thanks,
MLZ
 
Public Sub TransposePersonalData()
'ken johnson July 29, 2006
'transpose uneven sets of data........must have a blank row between
Application.ScreenUpdating = False
Dim rngData As Range
Dim iLastRow As Long
Dim I As Long
Dim iDataColumn As Integer
iDataColumn = Selection.Column
iLastRow = Cells(Application.Rows.Count, iDataColumn).End(xlUp).Row
I = Selection.Row - 1
Do While ActiveCell.Row < iLastRow
I = I + 1
Set rngData = Range(ActiveCell, ActiveCell.End(xlDown))
rngData.Copy
Cells(I, iDataColumn + 1).PasteSpecial transpose:=True
rngData.Cells(rngData.Cells.Count + 2, 1).Activate
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
Back
Top