Vertical data to horizontal

[Stephen de Cadenet]

Hi

I have a large list of addresses laid out vertically in Excel. Each new address has a blank line between.

The problem I have is that each address has a different number of lines. Where a list has 6 lines each I have been using the formula =INDEX($A:$A,ROW(A1)*6-6+COLUMN(A1) assuming the data starts in cell A1.

I can't figure out how to do the transpose when the number of lines vary; some addresses have 3,4,5, 6 cells etc. I have attached an example file if that helps?

If anyone could help that would be amazing.

Thanks

Stephen

 

[r1c1]

@Stephen de Cadenet
Welcome to chandoo.org forums and thanks for posting your question.

You may use a couple of helper columns to solve this problems.

For the sake of simplicity, I have named the range of data as data. This optimizes the formulas.

Set up 2 columns - start & end and write these formulas:
Press ctrl+shift+Enter after typing these formulas.

C2 - Start =IF(ROW(A1)=1,0,SMALL(IF(data="",ROW(data)),ROW(A1)-1))+1
D2 - End =SMALL(IF(data="",ROW(data)),ROW(A1))-1

Then, you can use this formula in adjacent columns to fetch transposed addresses.

=IFERROR(INDEX(INDEX(data,$C2):INDEX(data,$D2),COLUMN(A1)),"")

See attached workbook.

 

[Stephen de Cadenet]

@Stephen de Cadenet
Welcome to chandoo.org forums and thanks for posting your question.

You may use a couple of helper columns to solve this problems.

For the sake of simplicity, I have named the range of data as data. This optimizes the formulas.

Set up 2 columns - start & end and write these formulas:
Press ctrl+shift+Enter after typing these formulas.

C2 - Start =IF(ROW(A1)=1,0,SMALL(IF(data="",ROW(data)),ROW(A1)-1))+1
D2 - End =SMALL(IF(data="",ROW(data)),ROW(A1))-1

Then, you can use this formula in adjacent columns to fetch transposed addresses.

=IFERROR(INDEX(INDEX(data,$C2):INDEX(data,$D2),COLUMN(A1)),"")

See attached workbook.

Awesome work than you very much.