Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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

3 Answers 3

Original code (to remove duplicates from a column: http://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
share|improve this answer

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.

share|improve this answer
    
noted your comments. done the advanced filtering nicely. Thanks. –  user245491 Aug 18 '13 at 1:35

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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