create 2 lists from one

palaeontology

Active Member
Joined
May 12, 2017
Messages
298
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,078,491
Messages
5,340,683
Members
399,389
Latest member
JayNExcel

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top