Break rows to tables

**mosheva** · 09-23-2013, 05:21 AM

Hello,

I have imported my contacts list to excel and all of my contacts are arranged on A1:A100

Its arrange as:
A1 Contact Name
A2 Info
A3 Info
A4 Info
A5 Contact Name
A6 Info
A7 Info
A8 Info
A9 Contact Name
A10 etc,

I have used if formula to find only the names by (Mr or Ms) so on Column B1:B100 there is a tag "Name" next to each name and if false I used "Info".

I would like to make a range in between Contacts (A1:A4, A5:A8, A9:Etc,) and then use the transpose (paste special) in order to view them in rows
A1 Contact Name B1 Info C1 Info
A2 Contact Name B2 Info C2 Info

Any way to do it?

Thanks!

**Pete_UK** · 09-23-2013, 05:51 AM

Do you always have 4 rows of data for each contact name, so that the names are always 4 rows apart (rows 1,5,9,13,17,21 and so on)?

Pete

**mosheva** · 09-23-2013, 05:54 AM

Hi,

No.. sometimes more sometimes less...

**Pete_UK** · 09-23-2013, 05:58 AM

So, column B is used to indicate the start of a new record, with the tag "Name" ?

Can you post an example workbook (de-sensitized) ?

Pete

**mosheva** · 09-23-2013, 06:19 AM

Hi,

Sample attached

10X!Sample.xlsx

**Pete_UK** · 09-23-2013, 10:59 AM

I responded to this thread earlier, but it seems to have disappeared.

Put the number 1 in cell C1 and this formula in C2:

=IF(B2="Contact",INT(MAX(C$1:C1))+1,IF(ISNUMBER(FIND("@",A2)),ROUND(INT(C1)+0.1,2),IF(ISNUMBER(SEARCH("phone",A2)),ROUND(INT(C1)+0.08,2),IF(ISNUMBER(SEARCH("fax",A2)),ROUND(INT(C1)+0.09,2),IF(ISNUMBER(FIND("-",RIGHT(A2,5))),ROUND(INT(C1)+0.05,2),ROUND(C1+0.01,2))))))

and copy this down to the bottom of your data. This is longer than the previous formula that I gave, as it attempts to align the email, phone, fax and zip-code into the same consistent columns. Then put this formula in E1:

=IFERROR(INDEX($A:$A,MATCH(ROUND(ROWS($1:1)+(COLUMNS($E:E)-1)/100,2),$C:$C,0)),"")

and copy across to, say, P1. Then copy E1:P1 down as far as you need to.

The attached file demonstrates this with your sample data.

Hope this helps.

Pete