0

enter image description here

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?

  • I have a couple of suggestions for improving the question i.e. to help others help you. 1) Include the version of Office that your are using. 2) Include the data as text (nobody wants to type them all in) 3) Let us know what you've already tried after researching the question. – Chris Aug 17 '13 at 17:37
  • @nixda thanks for the macro. is it possible to include the filtering out a blacklist in the same macro too? – user245491 Aug 18 '13 at 1:40
  • @user245491 I have posted a working version for your case – nixda Aug 18 '13 at 9:59
1

Try using the advanced filter as described by Microsoft: http://support.microsoft.com/kb/262277

To create a compiled list of unique records, follow these steps:

  1. Select the column titles above rows or records that you want to sort.

  2. 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?

  3. Under Action, click Copy to another location.

  4. Click to select the Unique records only check box.

  5. In the List range box, type or select the range of records (such as A3:H10).

  6. In the Criteria range box, type or select the same range of records (that is, A3:H10)

  7. 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.

  8. 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.

  • noted your comments. done the advanced filtering nicely. Thanks. – user245491 Aug 18 '13 at 1:35
0

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

Modification

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

Final code

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

Notes

  1. To use VBA macros go to Developer » Visual basic » paste the code somewhere
  2. Have in mind, that this code only works for sorted lists. So sort your data column before.
  3. You have to put your cursor at the start of your list and run the macro via Alt+F8
  4. You have to change Range("B2:B7") accordingly to the range of your own blacklist
  5. You can easily switch from blacklist to whitelist concept by removing the Not from Not foundit Is Nothing
0

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.

0

Do the followings:

  1. Write this Formula in B2 & fill it down.

    =Countif(A$2:A2,A2)

  2. 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.

  3. Click on the Filter button in the Data Tab to apply the Autofilter.

  4. From column B select rows that are not equal to 1.

  5. You can then Delete these rows by highlighting them, and selecting Delete Rows.

  6. Finally remove the Filter& you will be left with Unique Cell numbers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.