How to duplicate or repeat data

[pr103]

I have a column: Name, Phone Number, Address (each in separate rows).

I'm trying to duplicate these three texts into one column
For example:

Name
Phone Number
Address
Name
Phone Number
Address....

and so on..

How can I achieve this in an easier way?

 

[Smitty]

Welcome to the Board!

Can I ask why? The structure you have now is perfect, but what you propose is all but useless in terms of being able to do anything with the data.

 

[Lidsavr]

Smitty,
It sounds like he is trying to create a telephone book to print out. If this is the case, a macro is the best way to do it.

Here is code I wrote quickly. Copy and paste it into the macro editor.

It assumes everything is columns A, B, & C and that Row 1 is the header.

Also Smitty, if you see how the code can be improved, I am all for learning. You are much better at this than I!

The code is:

Sub Transpose_Names()
    Do
        Range("A2:C2").Select
        Selection.Copy
        If Range("D1").Value = "" Then
            Range("D1").Select
        Else
            Range("D1").Select
            Selection.End(xlDown).Select
            ActiveCell.Offset(2, 0).Select
        End If
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        Range("A2:C2").Select
        Selection.Delete Shift:=xlUp
        Range("A2:C2").Select
    Loop Until Range("A2").Value = ""
    Range("A:C").Select
    Selection.Delete Shift:=xlToLeft
End Sub

Charles

 

[Smitty]

It sounds like he is trying to create a telephone book to print out. If this is the case, a macro is the best way to do it.

2 words: Mail Merge

 

[Lidsavr]

Smitty,
Your two words beat my many. I didn't think of using mail merge.

Thanks,

Charles

 

[pr103]

Any easier way without copying and pasting down the column?

 

[Smitty]

Any easier way without copying and pasting down the column?

I don't know why you want to, but this should do what you want:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> x = 2 <SPAN style="color:#00007F">To</SPAN> Cells(Rows.Count, "A").End(xlUp).Row<br>            <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> 3<br>                Cells(x, y).Copy Cells(Rows.Count, "E").End(xlUp).Offset(1)<br>            <SPAN style="color:#00007F">Next</SPAN> y<br>        <SPAN style="color:#00007F">Next</SPAN> x<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[pr103]

First macro is not working.
The second macro shifts 'phone' and 'address' to the right.
Try the macro yourself.
I've started name, phone and address at the beginning (A1) in column A.

 

[Smitty]

The second macro shifts 'phone' and 'address' to the right.
Try the macro yourself.

Really? This is what I get:

Excel 2010<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Name</TD><TD>Phone</TD><TD>Address</TD><TD style="TEXT-ALIGN: right"></TD><TD>Combined</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Tom</TD><TD style="TEXT-ALIGN: right">1234</TD><TD>aaaa</TD><TD style="TEXT-ALIGN: right"></TD><TD>Tom</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Fred</TD><TD style="TEXT-ALIGN: right">5678</TD><TD>bbbb</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1234</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Al</TD><TD style="TEXT-ALIGN: right">4567</TD><TD>cccc</TD><TD style="TEXT-ALIGN: right"></TD><TD>aaaa</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Lou</TD><TD style="TEXT-ALIGN: right">9876</TD><TD>dddd</TD><TD style="TEXT-ALIGN: right"></TD><TD>Fred</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">5678</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>bbbb</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Al</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">4567</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>cccc</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Lou</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">9876</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>dddd</TD></TR></TBODY></TABLE>

Sheet1​

Still sure it doesn't work?

 

[pr103]

Name in A1
Phone in A2
Address in A3
Name in A4
and repeat...