[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Removing Duplicates From A List

Posted on 2014-01-29
6
Medium Priority
?
240 Views
Last Modified: 2014-01-30
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++

Thank you in advance.
0
Comment
Question by:U_S_A
6 Comments
 
LVL 23

Accepted Solution

by:
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

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

Open in new window

Column with Counts
Then apply filters and select only 1 in the count column:
FilterResultExample.xlsx
0
 
LVL 2

Expert Comment

by:bearblack
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

Open in new window

End Sub
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 2

Expert Comment

by:bearblack
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

Open in new window

Forgot the line to properly sort
0
 
LVL 22

Expert Comment

by:Anne Troy
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

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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question