Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Removing Duplicates From A List

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
U_S_A
Asked:
U_S_A
1 Solution
 
NBVCCommented:
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
 
Angelp1ayCommented:
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
 
bearblackCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bearblackCommented:
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
 
DreamboatCommented:
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
 
bearblackCommented:
Sorry -- Missed the OO part thanks for the catch
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now