HOW DO I REMOVE UNIQUE VALUES IN EXCEL?

  • Thread starter Thread starter andy
  • Start date Start date
No, you can remove the duplicate ones, then you are left with the unique
ones and an move those wherever you want. In all other cases you would need
to use a filter

--


Regards,


Peo Sjoblom
 
Let's say we have a list of entries in column A and want to remove the unique
ones. Enter and run this macro:

Sub dropum()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
v = Cells(i, 1).Value
k = Application.WorksheetFunction.CountIf(Range("A:A"), v)
'MsgBox (v & " " & k)
If k = 1 Then
Cells(i, 1).Delete
End If
Next
End Sub
 
Try this:

=IF(ISERR(SMALL(IF(COUNTIF(Rng,Rng)>1,ROW(INDIRECT("1:"&ROWS(Rng)))),ROWS($1:1))),"",INDEX(Rng,SMALL(IF(COUNTIF(Rng,Rng)>1,ROW(INDIRECT("1:"&ROWS(Rng)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Unique Values 4
Excel Pivot Table - Counting Unique Values within one column of da 1
How to assign unique number to column duplicates? 3
How can excel generate every unique pair from a set of numbers? 4
Autogroup then sum - can it be done? 5
Keep all cells in a column unique? No cells identical 1
counting unique items within categories - but EXCLUDING blank cell 5
How to filter Unique values from Duplicates 6

Back
Top