Transpose
 

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

Transpose
 

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

On Jun 6, 11:58*am, JG wrote:
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

Transpose
 

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

"Imonit" wrote:

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

On Jun 6, 11:58 am, JG wrote:
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

Transpose
 

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.

On Jun 6, 12:38 pm, JG wrote:
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

"Imonit" wrote:
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

On Jun 6, 11:58 am, JG wrote:
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

Transpose
 

Awesome. Thank you. You just saved me hours of work!!

"Reitanos" wrote:

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.

On Jun 6, 12:38 pm, JG wrote:
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

"Imonit" wrote:
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

On Jun 6, 11:58 am, JG wrote:
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

Transpose
 

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

"Reitanos" wrote:

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.

On Jun 6, 12:38 pm, JG wrote:
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

"Imonit" wrote:
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

On Jun 6, 11:58 am, JG wrote:
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

Transpose
 

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

Maybe something like this would help.

Put the list in column B of Sheet1.

In Sheet1!A1 put
1

In Sheet1!A2 put
2

In Sheet1!A3 put
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",A2+1))
and extend down to the end of the list.

Next go to Sheet2, and put in Sheet2!A1
=IF(ISNA(
VLOOKUP(COLUMN()+100*(ROW()-1),Sheet1!$A:$B,2,FALSE)),"",
VLOOKUP(COLUMN()+100*(ROW()-1),Sheet1!$A:$B,2,FALSE))
Extend A1 to F1, then extend A1:F1 down as far as needed.

Transpose
 

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

On Tue, 24 Jun 2008 18:30:18 -0500, MyVeryOwnSelf wrote:

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

Maybe something like this would help.

Put the list in column B of Sheet1.

In Sheet1!A1 put
1

In Sheet1!A2 put
2

In Sheet1!A3 put
=IF(A2="",100*INT(A1/100)+101,IF(TRIM(B3)="","",A2+1))
and extend down to the end of the list.

Next go to Sheet2, and put in Sheet2!A1
=IF(ISNA(
VLOOKUP(COLUMN()+100*(ROW()-1),Sheet1!$A:$B,2,FALSE)),"",
VLOOKUP(COLUMN()+100*(ROW()-1),Sheet1!$A:$B,2,FALSE))
Extend A1 to F1, then extend A1:F1 down as far as needed.