Transpose Addresses in Column to Row

O

Oh_man

New Member
Joined
May 27, 2014
Messages
2
Hi,

I'm trying to transpose addresses in my "Address" column to a row and then scan down to the next block and repeat.

If I can have my cake and eat it too, then it'd be awesome to have the dissimilar (shorter) addresses lined up with the longer ones. I.e. those without a suite or room number would match those that have it and would simply not have and data in the "address 2" field. (3 fields used vs 4 fields used)

I've tried hacking peoples scripts from other projects(searching google) and some formulas, but I'm just not understanding it and am open to any suggestions at this point.

Thanks,
Chris

data looks like this:
[TABLE="width: 700, align: left"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Address
[/TD]
[TD]Address2
[/TD]
[TD]City
[/TD]
[TD]Country
[/TD]
[/TR]
[TR]
[TD]Office1
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]3532 Hayden Ave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office1
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 152"]
<colgroup><col width="152"></colgroup><tbody>[TR]
[TD="class: xl65, width: 152"]Beverly Hills, CA 90210[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office1
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 78"]
<colgroup><col width="78"></colgroup><tbody>[TR]
[TD="class: xl65, width: 78"]USA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]2000 Avenue Of The Stars[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]Ste 212[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]Beverly Hills, CA 90212[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 78"]
<colgroup><col width="78"></colgroup><tbody>[TR]
[TD="class: xl65, width: 78"]USA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office3
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]350 S Beverly Dr
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office3
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col style="width: 146px"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]London WC2H 8DL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office3
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]UK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office4
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]9111 Wilshire Blvd
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office4
[/TD]
[TD]Vancouver, BC V5K 1P3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office4
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]Canada[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


























I'm trying to make it look like this:
[TABLE="width: 700, align: left"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Address
[/TD]
[TD]Address2
[/TD]
[TD]City
[/TD]
[TD]Country
[/TD]
[/TR]
[TR]
[TD]Office1
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]3532 Hayden Ave[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Beverly Hills, CA 90210
[/TD]
[TD]USA
[/TD]
[/TR]
[TR]
[TD]Office1
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 152"]
<colgroup><col width="152"></colgroup><tbody>[TR]
[TD="class: xl65, width: 152"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]2000 Avenue Of The Stars[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Ste 212
[/TD]
[TD]Beverly Hills, CA 90212
[/TD]
[TD]USA
[/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office2
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 78"]
<colgroup><col width="78"></colgroup><tbody>[TR]

[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office3
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]350 S Beverly Dr
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]London WC2H 8DL
[/TD]
[TD]UK
[/TD]
[/TR]
[TR]
[TD]Office3
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col style="width: 146px"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office3
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]

[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office4
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"]9111 Wilshire Blvd
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Vancouver, BC V5K 1P3
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146"]Canada
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Office4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office4
[/TD]
[TD][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]
[TD="class: xl65, width: 146"][TABLE="width: 146"]
<colgroup><col width="146"></colgroup><tbody>[TR]

[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hello Chris


<colgroup><col style="width: 28ptpx"><col width="39pt"><col width="123,75pt"><col width="36,75pt"><col width="36,75pt"><col width="36,75pt"><col width="123,75pt"><col width="48pt"><col width="112,5pt"><col width="42pt"></colgroup><tbody>
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]Address Field[/TD]
[TD="align: left"]Helper[/TD]
[TD="align: left"]Helper[/TD]
[TD="align: left"]Helper[/TD]
[TD="align: left"]Address[/TD]
[TD="align: left"]Address2[/TD]
[TD="align: left"]City[/TD]
[TD="align: left"]Country[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]Office1[/TD]
[TD="align: left"]3532 Hayden Ave[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: left"]3532 Hayden Ave[/TD]
[TD="align: left"] [/TD]
[TD="align: left"]Beverly Hills, CA 90210[/TD]
[TD="align: left"]USA[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]Office1[/TD]
[TD="align: left"]Beverly Hills, CA 90210[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]Office1[/TD]
[TD="align: left"]USA[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]Office2[/TD]
[TD="align: left"]2000 Avenue Of The Stars[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: left"]2000 Avenue Of The Stars[/TD]
[TD="align: left"]Ste 212[/TD]
[TD="align: left"]Beverly Hills, CA 90210[/TD]
[TD="align: left"]USA[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]Office2[/TD]
[TD="align: left"]Ste 212[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]Office2[/TD]
[TD="align: left"]Beverly Hills, CA 90210[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]Office2[/TD]
[TD="align: left"]USA[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]Office3[/TD]
[TD="align: left"]350 S Beverly Dr[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: left"]350 S Beverly Dr[/TD]
[TD="align: left"] [/TD]
[TD="align: left"]London WC2H 8DL[/TD]
[TD="align: left"]UK[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]Office3[/TD]
[TD="align: left"]London WC2H 8DL[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]Office3[/TD]
[TD="align: left"]UK[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]Office4[/TD]
[TD="align: left"]9111 Wilshire Blvd[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: left"]9111 Wilshire Blvd[/TD]
[TD="align: left"] [/TD]
[TD="align: left"]Vancouver, BC V5K 1P3[/TD]
[TD="align: left"]Canada[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: left"]Office4[/TD]
[TD="align: left"]Vancouver, BC V5K 1P3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]Office4[/TD]
[TD="align: left"]Canada[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]
[TD="align: left"] [/TD]

</tbody>

ZelleFormel
C2=COUNTIF($A$2:$A$14,A2)
D2=COUNTIF($A$2:A2,A2)
E2=MATCH(A2,$A$2:$A$14,0)
F2=IF(D2=1,INDEX($B$2:$B$14,E2),"")
G2=IF(C2=4,IF(D2=1,INDEX($B$2:$B$14,E2+1),""),"")
H2=IF(C2=4,IF(D2=1,INDEX($B$2:$B$14,E2+2),""),IF(D2=1,INDEX($B$2:$B$14,E2+1),""))
I2=IF(C2=4,IF(D2=1,INDEX($B$2:$B$14,E2+3),""),IF(D2=1,INDEX($B$2:$B$14,E2+2),""))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Macro code to transpose specific cells in each row depending on text in that rows "Address" column
Replies
2
Views
546
Ljwilli24
L
A
  • Question Question
Need to sort entire row by column A (ascending)
Replies
1
Views
2K
Technowolf
Technowolf
S
  • Question Question
Excel finding Duplicates From a Range of values of a column
Replies
1
Views
964
DILIPandey
DILIPandey
D
  • Question Question
Parse Adress into seperate columns
Replies
1
Views
1K
mikerickson
mikerickson
J
  • Question Question
Macro Help: 6 Sets of Addresses in single row --> Export to new workbook, each address on a new sheet preceded by the data in columns A-D
Replies
2
Views
604
jeffcoleky
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top