Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Removing Duplicates From A List

Posted on 2014-01-29
Medium Priority
236 Views
I have a list that i need to remove all Duplicates, if I find any,

example
List
1
1
2
3
3
4
5
5

Goal
2
4

I have open office calc and I have Notepadd++

0
Question by:U_S_A
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Accepted Solution

NBVC earned 2000 total points
ID: 39819297
Assuming data in is A2 down.. then in B2 add a helper formula:

=COUNTIF(\$A\$2:\$A\$3;A2)=1

copied down

Then Select A1:B (last row) and go to Data|Filter|Autofilter.

Filter column B by 0 (False)

Select those rows, right click and delete rows.

Then remove the filter and delete column B
0

LVL 11

Expert Comment

ID: 39819298
Just add a count column next to your list in Excel with the formula:
=COUNTIF(A:A,A2)

Then apply filters and select only 1 in the count column:
Example.xlsx
0

LVL 2

Expert Comment

ID: 39819353
Dim Rw As Double, WS As Worksheet

Sub Keep_Unique()

Set WS = Sheets("Sheet1")

Rw = WS.Cells(Rows.Count, 1).End(xlUp).Row

WS.Cells(1, 1).Value = "Values"
WS.Range(WS.Cells(2, 2), WS.Cells(Rw, 2)).FormulaR1C1 "=IF(OR(RC[-1]=R[-1]C[-1],RC[-1]=R[1]C[-1]),1,0)"
WS.Range(WS.Cells(1, 1), WS.Cells(1, 2)).AutoFilter Field:=2, Criteria1:=1
WS.Range(WS.Cells(2, 1), WS.Cells(Rw, 2)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WS.Range(WS.Cells(1, 1), WS.Cells(Rw, 2)).AutoFilter
WS.Columns(2).Delete

End Sub
End Sub
0

LVL 2

Expert Comment

ID: 39819368
Sub Keep_Unique()

Set WS = Sheets("Sheet1")

Rw = WS.Cells(Rows.Count, 1).End(xlUp).Row

WS.Cells(1, 1).Value = "Values"
WS.Range(WS.Cells(1, 1), WS.Cells(Rw, 1)).Sort Key1:=WS.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
WS.Range(WS.Cells(2, 2), WS.Cells(Rw, 2)).FormulaR1C1 = "=IF(OR(RC[-1]=R[-1]C[-1],RC[-1]=R[1]C[-1]),1,0)"
WS.Range(WS.Cells(1, 1), WS.Cells(1, 2)).AutoFilter Field:=2, Criteria1:=1
WS.Range(WS.Cells(2, 1), WS.Cells(Rw, 2)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WS.Range(WS.Cells(1, 1), WS.Cells(Rw, 2)).AutoFilter
WS.Columns(2).Delete

End Sub
Forgot the line to properly sort
0

LVL 22

Expert Comment

ID: 39819407
bearblack, does OO really support VBA? Now I have to go look...

I am assuming OO does not have the remove duplicates feature like Excel 2007 has.

Looks like it's got something, which is described here:

http://www.wikihow.com/Remove-Duplicates-in-Open-Office-Calc
0

LVL 2

Expert Comment

ID: 39819453
Sorry -- Missed the OO part thanks for the catch
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll