Show contents of vertical cells in horizontal cells

Hello friends,

I am referring to the post made by NBVC at http://www.excelforum.com/excel-gene...xcel-help.html,
Try: =IF($A1="",INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"") in B2, copied across and down

I have a similar case but there are no empty rows between lists, what would be the formula to get the same results? (The transposed address should appear next to each first line of each block).

Earlier, I posted at http://www.excelforum.com/excel-gene...-one-cell.html and I got brilliant answers, I also would like to get benefit of the above mentioned formula but in cases when there are no empty rows between lists.

Any insight on this is highly appreciated


Many thanks

Is there something that is common in the first row of each block?

For example, will each first row start with salutation, like Mr or Mrs or Ms?

If yes, then using the same example as the linked thread, but with no spaces:

Ms a Sample
1 Sample Lane
Sample Town
Sample County
ME34 LALA
E-Mail: Sample@aol.ca
Ms a Sample
Sample Lane
Sample Town
Sample County
ME34 LALA
E-Mail: Sample@aol.com

Starting in A2, then in B2, enter formula:

=IF(OR(LEFT($A2,2)={"Mr","Ms","Mrs"}),INDIRECT("A"&ROW()-ROW($A$2)+COLUMN(B1)),"")

and copy it across and down the columns/rows.

You should get results like so:

Ms a Sample	Ms a Sample	1 Sample Lane	Sample Town	Sample County	ME34 LALA	E-Mail: Sample@aol.ca
1 Sample Lane						
Sample Town						
Sample County						
ME34 LALA						
E-Mail: Sample@aol.ca						
Ms a Sample	Ms a Sample	Sample Lane	Sample Town	Sample County	ME34 LALA	E-Mail: Sample@aol.com
Sample Lane						
Sample Town						
Sample County						
ME34 LALA						
E-Mail: Sample@aol.com

Hopefully you have a consitent number of items in each block or you may get some overlap...

I will asssume that you want each block to start where there is a new date entered in column A...seems logical.

So say in G2, enter formula:

=IF($A2<>"",D2,"")

and copy it only down the column as far as you want

In H2, enter formula:

=IF($A2<>"",IF(OR(G2="",INDIRECT("A"&ROW()-ROW($D$2)+COLUMN(C2))<>"",LOOKUP(REPT("z",255),B:B)=G2),"",INDIRECT("D"&ROW()-ROW($D$2)+COLUMN(C2))),"")

Copy it down the same number of rows and then copy across as many columns as you want to extract all vital information.

Note: Adjust the G2 in this formula if you didn't put the first formula in G2.

Hope this does it.