create 2 lists from one

[palaeontology]

Hi all,

I have the following data in the range C1:D15 ...

[TABLE="width: 462"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Original[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]Barber, Isabella (59792)[/TD]
[TD]melissa.barber@bigpond.com[/TD]
[/TR]
[TR]
[TD]Barber, Isabella (59792)[/TD]
[TD]robert.v.barber@hotmail.com[/TD]
[/TR]
[TR]
[TD]Bell, Maya (83298)[/TD]
[TD]gaye.grahambell@bigpond.com[/TD]
[/TR]
[TR]
[TD]Bellamywells, Lewis (64795)[/TD]
[TD]alanbw@ccpl.net[/TD]
[/TR]
[TR]
[TD]Bellamywells, Lewis (64795)[/TD]
[TD]bellamywells@hotmail.com[/TD]
[/TR]
[TR]
[TD]Brueckner, Amber (87862)[/TD]
[TD]leannebrue@hotmail.com[/TD]
[/TR]
[TR]
[TD]Brueckner, Amber (87862)[/TD]
[TD]rodrcsmith@gmail.com[/TD]
[/TR]
[TR]
[TD]Chapman, Jack (66886)[/TD]
[TD]david@propertynorth.com[/TD]
[/TR]
[TR]
[TD]Chapman, Jack (66886)[/TD]
[TD]dee@propertynorth.com[/TD]
[/TR]
[TR]
[TD]Combarngo, Xaire (83652)[/TD]
[TD]lspence731@gmail.com[/TD]
[/TR]
[TR]
[TD]Deeb, Sophie (72844)[/TD]
[TD]mldeeb@bigpond.com[/TD]
[/TR]
[TR]
[TD]Deighton, Fraser (88604)[/TD]
[TD]brett.deighton@bigpond.com[/TD]
[/TR]
[TR]
[TD]Deighton, Fraser (88604)[/TD]
[TD]jo.hardwick@bigpond.com[/TD]
[/TR]
[TR]
[TD]Green, Oscar (83077)[/TD]
[TD]nbgreen@bigpond.net.au[/TD]
[/TR]
</tbody>[/TABLE]

You'll notice that in the 'Original' column, a student name may appear more than once, which indicates they have more than one parent who supplied an email address (found in column D).

What I would like to do is to have column D converted to 2 lists, resembling the following ...

[TABLE="width: 406"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Parent A[/TD]
[TD]Parent B[/TD]
[/TR]
[TR]
[TD]melissa.barber@bigpond.com[/TD]
[TD]robert.v.barber@hotmail.com[/TD]
[/TR]
[TR]
[TD]gaye.grahambell@bigpond.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]alanbw@ccpl.net[/TD]
[TD]bellamywells@hotmail.com[/TD]
[/TR]
[TR]
[TD]leannebrue@hotmail.com[/TD]
[TD]rodrcsmith@gmail.com[/TD]
[/TR]
[TR]
[TD]david@propertynorth.com[/TD]
[TD]dee@propertynorth.com[/TD]
[/TR]
[TR]
[TD]lspence731@gmail.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mldeeb@bigpond.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brett.deighton@bigpond.com[/TD]
[TD]jo.hardwick@bigpond.com[/TD]
[/TR]
[TR]
[TD]nbgreen@bigpond.net.au[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd prefer this to be done by formula rather than code, if at all possible.

Kind regards,

Chris

 

[ravishankar]

Hi
if col C and D has the data, insert formula in col. E =if(countif($C$2:$C2,$C2)<=1,D2,"") Drag the formula till the last row.
in col F insert the formula =if(E2="",D2,"") drag till the last row.
Hope that helps
Ravi shankar