create 2 lists from one

palaeontology

Active Member
Joined
May 12, 2017
Messages
287
Hi all,

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

OriginalEmail
Barber, Isabella (59792)melissa.barber@bigpond.com
Barber, Isabella (59792)robert.v.barber@hotmail.com
Bell, Maya (83298)gaye.grahambell@bigpond.com
Bellamywells, Lewis (64795)alanbw@ccpl.net
Bellamywells, Lewis (64795)bellamywells@hotmail.com
Brueckner, Amber (87862)leannebrue@hotmail.com
Brueckner, Amber (87862)rodrcsmith@gmail.com
Chapman, Jack (66886)david@propertynorth.com
Chapman, Jack (66886)dee@propertynorth.com
Combarngo, Xaire (83652)lspence731@gmail.com
Deeb, Sophie (72844)mldeeb@bigpond.com
Deighton, Fraser (88604)brett.deighton@bigpond.com
Deighton, Fraser (88604)jo.hardwick@bigpond.com
Green, Oscar (83077)nbgreen@bigpond.net.au

<colgroup><col><col></colgroup><tbody>
</tbody>

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 ...

Parent AParent B
melissa.barber@bigpond.comrobert.v.barber@hotmail.com
gaye.grahambell@bigpond.com
alanbw@ccpl.netbellamywells@hotmail.com
leannebrue@hotmail.comrodrcsmith@gmail.com
david@propertynorth.comdee@propertynorth.com
lspence731@gmail.com
mldeeb@bigpond.com
brett.deighton@bigpond.comjo.hardwick@bigpond.com
nbgreen@bigpond.net.au

<colgroup><col><col></colgroup><tbody>
</tbody>

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

Kind regards,

Chris
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Forum statistics

Threads
1,077,649
Messages
5,335,487
Members
399,018
Latest member
KathyMoUHC

Some videos you may like

This Week's Hot Topics

Top