Extract only the unrepeated names from a list

[jammoca]

Please note, this is not a Unique List extraction ... that I can do.

I have a list of names in A2:A500, and their corresponding Form Class in B2:B500

some of those names occur more than once.

I'd like to create a list in D2:D500 of ONLY the names that occur once. Any name that occurs twice or more should not appear in the new list AT ALL. In column E2:E500, I'd like their corresponding form class to be returned.

I'd then like a list (In G2:G500) of ONLY the names that did repeat more than once, with their corresponding form class in H2:H500.

In other words, I'd like the original list to be split into two new lists, one with just the unrepeated names (and form classes) and one with the repeated names (and form classes).

Kind regards,

Chris

 

[Trebor76]

Hey Chris,

Try these formuals in the cells noted and then fill them down to row 500:

Cell D2: =IF(COUNTIF($A$2:$A$500,$A2)=1,$A2,"")
Cell E2: =IF(LEN($D2)=0,"",VLOOKUP($D2,$A$2:$B$500,2,FALSE))
Cell G2: =IF(COUNTIF($A$2:$A$500,$A2)>1,$A2,"")
Cell H2: =IF(LEN($G2)=0,"",VLOOKUP($G2,$A$2:$B$500,2,FALSE))

Regards,

Robert

 

[vogel997]

Simple solution:

Sam	Class 1					Sam	Class 1
Tom	Class 2		Tom	Class 2
Nick	Class 3		Nick	Class 3
Peter	Class 4		Peter	Class 4
John	Class 5					John	Class 5
Joe	Class 6		Joe	Class 6
Will	Class 7		Will	Class 7
Jim	Class 8		Jim	Class 8
Sam	Class 9					Sam	Class 9
John	Class 10					John	Class 10
Bill	Class 11		Bill	Class 11
Sarah	Class 12					Sarah	Class 12
Sid	Class 13		Sid	Class 13
Vern	Class 14		Vern	Class 14
Jill	Class 15		Jill	Class 15
Sarah	Class 16					Sarah	Class 16

<colgroup><col span="8"></colgroup><tbody>
</tbody>

	Formulas
D2	=IF(COUNTIF($A$2:$A$500,A3)=1,A3,"")
E2	=IF(D2="","",B2)
G2	=IF(COUNTIF($A$2:$A$500,A2)>1,A2,"")
H2	=IF(G2="","",B2)

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

 

[xpluto]

Hi Chris,

vogel997's solution will work. And here's an alternative you can try if you do not want the empty rows in your list. (Please note: this way you will need two extra hidden columns. in this example, I've placed my hidden columns in column C & D, but you can use any columns)

Here are the formulas:

Column C (Hidden column) - =IF(COUNTIF($A$2:$A$500,A2)=1,ROW(),"")
Column D (Hidden column) - =IF(COUNTIF($A$2:$A$17,A2)>1,ROW(),"")
Column E - =IFERROR(INDEX($A:$B,SMALL(C:C,ROW()-1),1),"")
Column F - =IFERROR(INDEX($A:$B,SMALL(C:C,ROW()-1),2),"")
Column G - =IFERROR(INDEX($A:$B,SMALL(D:D,ROW()-1),1),"")
Colum H - =IFERROR(INDEX($A:$B,SMALL(D:D,ROW()-1),2),"")

Hope this helps =)

Cheers,
V