# Extract only the unrepeated names from a list

#### jammoca

##### Banned
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Trebor76

##### Well-known Member
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

##### Active Member
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

##### New Member
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

Replies
6
Views
243
Replies
3
Views
135
Replies
3
Views
184
Replies
9
Views
287
Replies
8
Views
318

1,126,972
Messages
5,621,895
Members
415,864
Latest member
cybid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back