After using this formula =IF(ISERROR(MATCH(A2,B:B,0)),A2,"")
in column C, it nicely filters out the blacklisted numbers.
But I need advice for a step further: How do I remove duplicates?
After using this formula =IF(ISERROR(MATCH(A2,B:B,0)),A2,"")
in column C, it nicely filters out the blacklisted numbers.
But I need advice for a step further: How do I remove duplicates?
Try using the advanced filter as described by Microsoft: http://support.microsoft.com/kb/262277
Select the column titles above rows or records that you want to sort.
On the Data menu, point to Filter, and then click Advanced Filter. If you are prompted as follows, click OK. No headers detected. Assume top row of selection is header row?
Under Action, click Copy to another location.
Click to select the Unique records only check box.
In the List range box, type or select the range of records (such as A3:H10).
In the Criteria range box, type or select the same range of records (that is, A3:H10)
In the Copy to box, type the address of or select the first cell where you want the information to be placed. Make sure there is enough room so you don't overwrite pre-existing data.
Have in mind, that you can only do this in the active sheet.
Click OK.
Notes
If the first record of original data is duplicated, it appears twice in the new list. Just hide the first line.
If you perform the Advanced Filter command in-place, the sheet still contains all records. Duplicate records are hidden. To work around this behavior, point to Rows on the Format menu, and then click Unhide.
Original code (to remove duplicates from a column: https://superuser.com/a/49618/50173)
'Remove duplicates from SORTED list
Sub getDistinct()
Do While ActiveCell.Value <> ""
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub
In the line If ActiveCell.Value = ActiveCell.Offset(1, 0).Value
the macro checks if the next cell below the current one has the same value. If yes, it deletes the current one and moves on to the next cell.
You can add a second condition to the IF statement, so it would also delete cells which are in your blacklist. Here's the pseudocode.
if ActiveCell = NextCell OR ActiveCell = FoundinBlacklist then delete Activecell
Sub getDistinct()
Do While ActiveCell.Value <> ""
On Error Resume Next
Set foundit = Range("B2:B7").Find(ActiveCell, lookat:=xlWhole)
On Error GoTo 0
If ActiveCell = ActiveCell.Offset(1, 0) Or Not foundit Is Nothing Then
ActiveCell.Select
Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub
Developer » Visual basic
» paste the code somewhereRange("B2:B7")
accordingly to the range of your own blacklistNot
from Not
foundit Is Nothing
You can change the formula to:
=IF(ISNA(MATCH(A2,B:B,0)) * ISNA(MATCH(A2,C$1:C1,0)),A2,"")
It won't display duplicated records. You can then use AutoFilter, excluding (Blanks)
, and copy&paste values to a new sheet.
Do the followings:
Write this Formula in B2
& fill it down.
=Countif(A$2:A2,A2)
Counif
function will generate list of 1's
& 2's
, now you need to delete the rows for which the count is greater than 1
.
Click on the Filter
button in the Data Tab
to apply the Autofilter
.
From column B
select rows that are not equal to 1
.
You can then Delete these rows by highlighting them, and selecting Delete Rows
.
Finally remove the Filter
& you will be left with Unique Cell numbers.