Conditionally Transpose Rows to Columns

[LinkSome]

Hello,

I had originally posted this last week and thought a separate post answered my question. However, I cannot modify that formula well enough to work here so I'm posting again! I'm looking to move values that show as additional rows to a separate column within the row it is truly associated with. The complete range of the spreadsheet is very large, but for an example I'll include three instances.

There are a total of three potential address lines. Ocassionally the first is blank but the second is filled, generally the third is not used but it does make an appearance every thousand rows or so. I would like for each potential address line to move up to the row it is associated with. So if Address 1 was blank but 2 and 3 had contents (separate rows in address column below), the Address 2 field would fill the blank Address 1 and and Address 3 would fill Address 2 next to it. If all three had contents, it would transpose across the three fields. The city is generally the last row in the address column that is associated with a specific instance, though sometimes that would have missing data as well. I would like that transposed across to a separate column, City. Finally, the State and Zip appear on the row with the City but that is below the instance it is associated with. If those items could be moved up, or everything else moved down, it would be a great help.

test

*

A

B

C

D

E

F

G

H

I

J

K

L

M

OWNER NAME

ADDRESS

ADDRESS 2

ADDRESS 3

CITY

STATE

ZIP

HOLDER NAME

REPORT YEAR

PROPERTY TYPE

PROPERTY DESCRIPTION

ID

CASH

XXX FREK

PO BOX AAA

*

*

*

*

*

AAA

DIVIDENDS

*

775 AAA STREET

*

*

*

*

*

*

*

*

*

*

*

*

STAMFORD

*

*

*

CA

06902-6828

*

*

*

*

*

*

YYY JAMES S

*

*

*

*

*

*

AAA

DIVIDENDS

*

CIO AAA CO

*

*

*

*

*

*

*

*

*

*

*

*

STAMFORD

*

*

*

CA

06926-700

*

*

*

*

*

*

ZZZ WILLIAM

*

*

*

*

*

*

AAA

DIVIDENDS

*

CIO AAA CO

*

*

*

*

*

*

*

*

*

*

*

*

STAMFORD

*

*

*

CA

06926-700

*

*

*

*

*

*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]773[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]778[/TD]
[TD="align: right"]33.6[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]780[/TD]
[TD="align: right"]50.4[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

to

test

*

A

B

C

D

E

F

G

H

I

J

K

L

M

OWNER NAME

ADDRESS

ADDRESS 2

ADDRESS 3

CITY

STATE

ZIP

HOLDER NAME

REPORT YEAR

PROPERTY TYPE

PROPERTY DESCRIPTION

ID

CASH

XXX FREK

PO BOX AAA

775 AAA STREET

*

STAMFORD

CA

06902-6828

AAA

DIVIDENDS

YYY JAMES S

CIO AAA CO

*

*

STAMFORD

CA

06926-700

AAA

DIVIDENDS

ZZZ WILLIAM

CIO AAA CO

*

*

STAMFORD

CA

06926-700

AAA

DIVIDENDS

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:156px;"><col style="width:125px;"><col style="width:105px;"><col style="width:73px;"><col style="width:76px;"><col style="width:44px;"><col style="width:75px;"><col style="width:98px;"><col style="width:89px;"><col style="width:103px;"><col style="width:158px;"><col style="width:28px;"><col style="width:42px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]773[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]778[/TD]
[TD="align: right"]33.6[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1994[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]780[/TD]
[TD="align: right"]50.4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[LinkSome]

Sorry, I have been away for school. This works great! Thank you so much.

 

[hiker95]

LinkSome,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.