Find and Findnext code help

[SteelNuttz]

Hey guys, I have a long list of contact names and addresses, none of which have a unique identifier. I need a way to pull the contact name and address info from the list, and paste it into a new tab so that I can have all of the contact names listed as column headers.

an example of two contacts might look like this:

Contact	John Doe
Address 1	1 Main St
Address 2	City, St Zip
Contact	Jane Doe
Address 1	2 Main St
Address 2	City, St Zip

<tbody>
</tbody>

So far I can only envision doing a "Find" in Column A for "Contact", then offsetting 1 column, selecting Activecell and the 2 cells below it, and pasting in a new tab. I can get the first one to go, I'm not sure how to continue the loop to "FindNext" and do the same thing.

The number of times I'll need to do this is variable, so it could be 1 contact, it could be 10 contacts.

Any help would be appreciated, thanks!

 

[BlueHornet]

If your data are somewhat regular (meaning that you may have some contacts with 4 lines and some with 3 lines) then the following might work to make a column of individual lines which you can then filter / copy into a new sheet. If the data aren't this regular, then you could still do something similar, but you'd need more conditionals in the extraction formula:

	A	B	C	D
1	Contact	John Doe		John Doe / 1 Main St / City, St Zip
2	Address 1	1 Main St
3	Address 2	City, St Zip
4	Contact	Jane Doe		Jane Doe / 2 Main St / City, St Zip / Country
5	Address 1	2 Main St
6	Address 2	City, St Zip
7	Address 3	Country

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D1 =IF( A1 = "Contact", B1 & " / " & B2 & " / " & B3 & IF( A4 <> "Contact", " / " & B4, ""), "") D2 =IF( A2 = "Contact", B2 & " / " & B3 & " / " & B4 & IF( A5 <> "Contact", " / " & B5, ""), "") D3 =IF( A3 = "Contact", B3 & " / " & B4 & " / " & B5 & IF( A6 <> "Contact", " / " & B6, ""), "") D4 =IF( A4 = "Contact", B4 & " / " & B5 & " / " & B6 & IF( A7 <> "Contact", " / " & B7, ""), "") D5 =IF( A5 = "Contact", B5 & " / " & B6 & " / " & B7 & IF( A8 <> "Contact", " / " & B8, ""), "") D6 =IF( A6 = "Contact", B6 & " / " & B7 & " / " & B8 & IF( A9 <> "Contact", " / " & B9, ""), "") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>