Column to a row and repeat!

[redtiger]

Hi,

I hope someone can help me with this annoying problem. I have some information in a column like this:

<TABLE style="WIDTH: 63pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=84><COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 63pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=20 width=84>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Address 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Telephone 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Address 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Telephone 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Name 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Address 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Telephone 3</TD></TR></TBODY></TABLE>
etc, etc

I would like to seperate the information out into various columns: Name, Company (I will have to add this info seperately), Address, and Telephone. Has anyone got any tips and tricks that might help me to do this without having to cut and paste everything across. I tried to record a macro but the Address 2 and Telephone 2 moved in to the Address 1 and Telephone 1 column.

Many thanks!

 

[stnkynts]

Have you tried the PasteSpecial Transpose option. I think this is what you are trying to do. If it is not let me know.

 

[hiker95]

redtiger,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[MikeWx]

redtiger,

Assuming your information is in A1:A300, type the following formula in Cell B1, then copy it across to C1 and D1. Copy the three cells down 99 more rows to reformat all the information in A1:A300. Afterwards, insert a row between columns B and C to add your Company information.

=INDEX($A$1:$A$300,COLUMN(A1)+(3*ROW(A1)-3),1)

 

[shadow12345]

Have you tried the PasteSpecial Transpose option. I think this is what you are trying to do. If it is not let me know.

Im with you on this one... sounds like transpose and then just insert a column for Company.

Copy the data
Copy your data range. The quickest way to do this is to use the keyboard shortcut, Control + C.

Open the Paste Special menu
Go to the cell in the top left corner where you would like your new transposed data to be placed. Open the Edit menu on the main toolbar in Microsoft Excel, then select Paste Special and transpose

 

[mirabeau]

Hi,

Try

Sub test()
Dim lr As Long, e As Range, a, k As Long
lr = Cells(Rows.Count, 1).End(3).Row
a = Cells(2, 1).Resize(lr - 1)
For Each e In [c2].Resize(Int(lr / 4) + 1, 3)
    k = k + 1
    If k Mod 4 <> 4 Then e.Value = a(k, 1)
Next e
[d1].Resize(k).Insert xlToRight
End Sub

 

[redtiger]

Thank you so much for all your responses!

In the end I tried MikeWX's suggestion and it worked a treat!

If I had manually cut and paste all the information it would have taken me days - with the help from you good people it took me less than 5 minutes!

Thanks again!

 

[mirabeau]

with the help from you good people it took me less than 5 minutes!

That's actually very slow to do this sort of problem, but it's your problem ...