Transpose Addresses in Column to Row

[Oh_man]

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:

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

<tbody>
</tbody>

I'm trying to make it look like this:

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

<tbody>
</tbody>

 

[shift-del]

Hello Chris

	A	B	C	D	E	F	G	H	I
1	Name	Address Field	Helper	Helper	Helper	Address	Address2	City	Country
2	Office1	3532 Hayden Ave	3	1	1	3532 Hayden Ave		Beverly Hills, CA 90210	USA
3	Office1	Beverly Hills, CA 90210	3	2	1
4	Office1	USA	3	3	1
5	Office2	2000 Avenue Of The Stars	4	1	4	2000 Avenue Of The Stars	Ste 212	Beverly Hills, CA 90210	USA
6	Office2	Ste 212	4	2	4
7	Office2	Beverly Hills, CA 90210	4	3	4
8	Office2	USA	4	4	4
9	Office3	350 S Beverly Dr	3	1	8	350 S Beverly Dr		London WC2H 8DL	UK
10	Office3	London WC2H 8DL	3	2	8
11	Office3	UK	3	3	8
12	Office4	9111 Wilshire Blvd	3	1	11	9111 Wilshire Blvd		Vancouver, BC V5K 1P3	Canada
13	Office4	Vancouver, BC V5K 1P3	3	2	11
14	Office4	Canada	3	3	11

<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>
</tbody>

Zelle	Formel
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>

 

[Oh_man]

Amazing. Thank you so much!