Arranging Data with Paste Special Transpose

[Dhivya]

Hey, I was wondering if someone could give me a code to help me rearrange some data on excel. So what I have on excel in column A is

<table border="0" cellpadding="0" cellspacing="0" height="478" width="131"><colgroup><col style="mso-width-source:userset;mso-width-alt:6546;width:134pt" width="179"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:134pt" height="20" width="179">Heba Gamil Elhawi</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">4190 Finch Ave E</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">Scarborough, ON M1T 3V9</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">416-297-0071</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl65" style="height:30.0pt;width:134pt" height="40" width="179">Email: h_elhawi@hotmail.com</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:134pt" height="20" width="179">Kathryn Ellis</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">738 Sheppard Avenue</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">Suite 201</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">North York, ON M2K 1C4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">416-223-2151</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">3330 Yonge Street</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">Toronto, ON M4N 2M4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">416-484-9944</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:134pt" height="20" width="179">Honey Lynda Elmaleh</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl65" style="height:30.0pt;width:134pt" height="40" width="179">25 Sheppard Ave W Suite 680</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">Toronto, ON M2N 6S6</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:134pt" height="20" width="179">416-225-1352</td> </tr> </tbody></table>

And what I want to do is make it so it all goes into one Row so in column A the name, B the street address, C City and Postal Code D Phone Number E, Fax, G Email and F Website.

Also, sometimes there may not be a Fax number or an email etc.

What I have been doing is copying and then clicking Paste Special and then clicking Transpose. I would really appreciate it if someone could help me with a code

Thanks!

 

[WhatTheXL]

If your Data is setup where the first name is on row 1
and the second name is a row 8, third name is on row 15.

7 rows for each address.

in cell B2 enter:

=INDIRECT(ADDRESS(COLUMNS($A$1:A$1)+ROWS($A$1:$A1)*7-7,1))

Copy over to Row H then copy down as far as you need it.
Select the entire range.
Copy > Paste Special > Values
Then switch columns G and H

 

[jberwald]

I had a similar problem and found that this worked for me

=OFFSET($A$1,(ROW()-1)*6+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

 

[Dhivya]

If your Data is setup where the first name is on row 1
and the second name is a row 8, third name is on row 15.

7 rows for each address.

in cell B2 enter:

=INDIRECT(ADDRESS(COLUMNS($A$1:A$1)+ROWS($A$1:$A1)*7-7,1))

Copy over to Row H then copy down as far as you need it.
Select the entire range.
Copy > Paste Special > Values
Then switch columns G and H

Since some of the rows don't have certain fields (i.e., some do not have an e-mail address or a website) there is not a concrete number of cells between each contact.

 

[Dhivya]

I had a similar problem and found that this worked for me

=OFFSET($A$1,(ROW()-1)*6+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

I pasted this into B2 and I got #REF!