Showing only unique values

danielrussell2

New Member
Joined
Mar 17, 2016
Messages
17
I have a column of names that I put together from two different lists. The list will mostly be duplicates, as they are similar sets of names from different sources. However, there will be some names that were included on one and not the other, or there may be different spelling/versions of the names (I've tried to include a decent sample of what I mean below).

Ben Amos
Benjamin Amos
Kenny Brothers
Larry Chisenhall
Larry Chisenhall
Charlie Chisenhall
Charlie Chisenhall
Derrick Lawrie
Derrick Lowrie
Ken Stiles
Kenneth Stiles
Walter Young
Walter Young

I would want the above list to remove all of the same duplicates (not just one of them as with the "remove duplicates" task with Data Tools) and leave any names that are unique, which from the above list, would give me:

Ben Amos
Benjamin Amos
Kenny Brothers
Derrick Lawrie
Derrick Lowrie
Ken Stiles
Kenneth Stiles

Any ideas on how to do this? Thanks!!!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
532
Office Version
  1. 365
Platform
  1. Windows
Will something like this work for you?
Code:
Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), unique:=True
 

uk747

Well-known Member
Joined
Jul 20, 2011
Messages
599
Click on Data/Filter/Advanced Filter then select unique records only.
 
Last edited:

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe something like....

c2=IFERROR(INDEX($A$2:$A$14,SMALL(IF(COUNTIF($A$2:$A$14,$A$2:$A$14)=1,ROW($A$2:$A$14)-ROW($A$2)+1),ROWS($C$2:C2))),"") Control Shift Enter


Row\Col
A​
B​
C​
2​
Ben AmosBen Amos
3​
Benjamin AmosBenjamin Amos
4​
Kenny BrothersKenny Brothers
5​
Larry ChisenhallDerrick Lawrie
6​
Larry ChisenhallDerrick Lowrie
7​
Charlie ChisenhallKen Stiles
8​
Charlie ChisenhallKenneth Stiles
9​
Derrick Lawrie
10​
Derrick Lowrie
11​
Ken Stiles
12​
Kenneth Stiles
13​
Walter Young
14​
Walter Young

<tbody>
</tbody>
 

danielrussell2

New Member
Joined
Mar 17, 2016
Messages
17

ADVERTISEMENT

maybe something like....

c2=IFERROR(INDEX($A$2:$A$14,SMALL(IF(COUNTIF($A$2:$A$14,$A$2:$A$14)=1,ROW($A$2:$A$14)-ROW($A$2)+1),ROWS($C$2:C2))),"") Control Shift Enter


Row\Col
A​
B​
C​
2​
Ben AmosBen Amos
3​
Benjamin AmosBenjamin Amos
4​
Kenny BrothersKenny Brothers
5​
Larry ChisenhallDerrick Lawrie
6​
Larry ChisenhallDerrick Lowrie
7​
Charlie ChisenhallKen Stiles
8​
Charlie ChisenhallKenneth Stiles
9​
Derrick Lawrie
10​
Derrick Lowrie
11​
Ken Stiles
12​
Kenneth Stiles
13​
Walter Young
14​
Walter Young

<tbody>
</tbody>

I must have messed something up when I changed the parameters of the table I'm trying to widdle down - goes from A1 to A2560, and I'm trying to put the new table starting in C1

=IFERROR(INDEX($A$1:$A$2560,SMALL(IF(COUNTIF($A$1:$A$2560,$A$1:$A$2560)=1,ROW($A$1:$A$2560)-ROW($A$1)+1),ROWS($C$1:C1))),"")
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
are you sure you commited the formula with control shift enter, not just enter ?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
danielrussell2,

If I understand you correctly, then here is a macro solution for you to consider.

Sample raw data, and, results:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ben Amos</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ben Amos</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Benjamin Amos</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Benjamin Amos</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kenny Brothers</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kenny Brothers</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Larry Chisenhall</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Larry Chisenhall</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Larry Chisenhall</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Charlie Chisenhall</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Charlie Chisenhall</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Derrick Lawrie</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Charlie Chisenhall</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ken Stiles</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Derrick Lawrie</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kenneth Stiles</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Derrick Lawrie</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Walter Young</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ken Stiles</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kenneth Stiles</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Walter Young</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Walter Young</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub GetUniqueNames()
' hiker95, 03/18/2016, ME928938
Dim r As Range, rng As Range
Application.ScreenUpdating = False
Set rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each r In rng
    If Not .Exists(r.Value) Then
      .Add r.Value, r.Value
    End If
  Next
  Range("C1").Resize(.Count) = Application.Transpose(Array(.Keys))
  Columns(3).AutoFit
End With
Application.ScreenUpdating = True
End Sub


How To Install Macros:

If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name ( GetUniqueNames ) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Hi danielrussel2,
Can you use something like this?
Mike Szczesny

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Ben  Amos</td><td style="text-align: right;;"></td><td style=";">Ben  Amos</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Benjamin Amos</td><td style="text-align: right;;"></td><td style=";">Benjamin Amos</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Kenny Brothers</td><td style="text-align: right;;"></td><td style=";">Kenny Brothers</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Larry Chisenhall</td><td style="text-align: right;;"></td><td style=";">Derrick Lawrie</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Larry Chisenhall</td><td style="text-align: right;;"></td><td style=";">Derrick Lowrie</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Charlie Chisenhall</td><td style="text-align: right;;"></td><td style=";">Ken Stiles</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Charlie Chisenhall</td><td style="text-align: right;;"></td><td style=";">Kenneth Stiles</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Derrick Lawrie</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Derrick Lowrie</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Ken Stiles</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Kenneth Stiles</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Walter Young</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Walter Young</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$1:$A$13,SMALL(<font color="Green">IF(<font color="Purple">FREQUENCY(<font color="Teal">IF(<font color="#FF00FF">$A$1:$A$13<>"",MATCH(<font color="Navy">$A$1:$A$13,$A$1:$A$13,0</font>)</font>),ROW(<font color="#FF00FF">$A$1:$A$13</font>)-ROW(<font color="#FF00FF">$A$1</font>)+1</font>)=1,ROW(<font color="Teal">$A$1:$A$13</font>)-ROW(<font color="Teal">$B$1</font>)+1</font>),ROWS(<font color="Purple">$C$1:C1</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top