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:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Contact[/TD]
[TD]John Doe[/TD]
[/TR]
[TR]
[TD]Address 1[/TD]
[TD]1 Main St[/TD]
[/TR]
[TR]
[TD]Address 2[/TD]
[TD]City, St Zip[/TD]
[/TR]
[TR]
[TD]Contact[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]Address 1[/TD]
[TD]2 Main St[/TD]
[/TR]
[TR]
[TD]Address 2[/TD]
[TD]City, St Zip[/TD]
[/TR]
</tbody>[/TABLE]

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
John Doe / 1 Main St / City, St Zip
Jane Doe / 2 Main St / City, St Zip / Country

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Contact[/TD]
[TD="bgcolor: #FAFAFA"]John Doe[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Address 1[/TD]
[TD="bgcolor: #FAFAFA"]1 Main St[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Address 2[/TD]
[TD="bgcolor: #FAFAFA"]City, St Zip[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Contact[/TD]
[TD="bgcolor: #FAFAFA"]Jane Doe[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Address 1[/TD]
[TD="bgcolor: #FAFAFA"]2 Main St[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Address 2[/TD]
[TD="bgcolor: #FAFAFA"]City, St Zip[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Address 3[/TD]
[TD="bgcolor: #FAFAFA"]Country[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=IF( A1 = "Contact", B1 & " / " & B2 & " / " & B3 & IF( A4 <> "Contact", " / " & B4, ""), "")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=IF( A2 = "Contact", B2 & " / " & B3 & " / " & B4 & IF( A5 <> "Contact", " / " & B5, ""), "")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF( A3 = "Contact", B3 & " / " & B4 & " / " & B5 & IF( A6 <> "Contact", " / " & B6, ""), "")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF( A4 = "Contact", B4 & " / " & B5 & " / " & B6 & IF( A7 <> "Contact", " / " & B7, ""), "")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=IF( A5 = "Contact", B5 & " / " & B6 & " / " & B7 & IF( A8 <> "Contact", " / " & B8, ""), "")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=IF( A6 = "Contact", B6 & " / " & B7 & " / " & B8 & IF( A9 <> "Contact", " / " & B9, ""), "")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]