Showing only unique values

[danielrussell2]

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

 

[MPW]

Will something like this work for you?

Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B1"), unique:=True

 

[uk747]

Click on Data/Filter/Advanced Filter then select unique records only.

 

[Weazel]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Ben Amos[/TD]
[TD][/TD]
[TD]Ben Amos[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Benjamin Amos[/TD]
[TD][/TD]
[TD]Benjamin Amos[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Kenny Brothers[/TD]
[TD][/TD]
[TD]Kenny Brothers[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Larry Chisenhall[/TD]
[TD][/TD]
[TD]Derrick Lawrie[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Larry Chisenhall[/TD]
[TD][/TD]
[TD]Derrick Lowrie[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Charlie Chisenhall[/TD]
[TD][/TD]
[TD]Ken Stiles[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Charlie Chisenhall[/TD]
[TD][/TD]
[TD]Kenneth Stiles[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Derrick Lawrie[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Derrick Lowrie[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Ken Stiles[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Kenneth Stiles[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Walter Young[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Walter Young[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[danielrussell2]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Ben Amos[/TD]
[TD][/TD]
[TD]Ben Amos[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Benjamin Amos[/TD]
[TD][/TD]
[TD]Benjamin Amos[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Kenny Brothers[/TD]
[TD][/TD]
[TD]Kenny Brothers[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Larry Chisenhall[/TD]
[TD][/TD]
[TD]Derrick Lawrie[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Larry Chisenhall[/TD]
[TD][/TD]
[TD]Derrick Lowrie[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Charlie Chisenhall[/TD]
[TD][/TD]
[TD]Ken Stiles[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Charlie Chisenhall[/TD]
[TD][/TD]
[TD]Kenneth Stiles[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Derrick Lawrie[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Derrick Lowrie[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Ken Stiles[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Kenneth Stiles[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Walter Young[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Walter Young[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

are you sure you commited the formula with control shift enter, not just enter ?

 

[hiker95]

danielrussell2,

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

Sample raw data, and, results:

Excel 2007
	A	B	C
1	Ben Amos		Ben Amos
2	Benjamin Amos		Benjamin Amos
3	Kenny Brothers		Kenny Brothers
4	Larry Chisenhall		Larry Chisenhall
5	Larry Chisenhall		Charlie Chisenhall
6	Charlie Chisenhall		Derrick Lawrie
7	Charlie Chisenhall		Ken Stiles
8	Derrick Lawrie		Kenneth Stiles
9	Derrick Lawrie		Walter Young
10	Ken Stiles
11	Kenneth Stiles
12	Walter Young
13	Walter Young
14
Sheet1

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

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.

 

[Mike Szczesny]

Hi danielrussel2,
Can you use something like this?
Mike Szczesny

Excel 2012
	A	B	C	D
1	Ben Amos		Ben Amos
2	Benjamin Amos		Benjamin Amos
3	Kenny Brothers		Kenny Brothers
4	Larry Chisenhall		Derrick Lawrie
5	Larry Chisenhall		Derrick Lowrie
6	Charlie Chisenhall		Ken Stiles
7	Charlie Chisenhall		Kenneth Stiles
8	Derrick Lawrie
9	Derrick Lowrie
10	Ken Stiles
11	Kenneth Stiles
12	Walter Young
13	Walter Young
Sheet1

Cell Formulas
Range	Formula
C1	{=IFERROR(INDEX($A$1:$A$13,SMALL(IF(FREQUENCY(IF($A$1:$A$13<>"",MATCH($A$1:$A$13,$A$1:$A$13,0)),ROW($A$1:$A$13)-ROW($A$1)+1)=1,ROW($A$1:$A$13)-ROW($B$1)+1),ROWS($C$1:C1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.