CODEWITHSUNDEEP
excelvba
loveforfire33
loveforfire33

Reputation: 1110

Excel unique value query

I'm not very experienced with excel -- I'm much more of a c# guy -- was hoping some of the excel gurus could help me out here!

Basically I have a spreadsheet that has only one column of text data (column a). I need to query this list of data.

I will be needing to basically copy in some more text data into another column (let's say column b), and then filter out the records in column b that are already present somewhere in column a, leaving me with only the unique records that are in column b, but not column a.

I've tried using the advanced filter but can't seem to get it to work. Any tips or advice on how I can do this would be great.

Thanks

Upvotes: 0

Views: 769

Answers (3)

panda-34
panda-34

Reputation: 4209

You can filter your data dynamically, say into column C with formulas like

=IF(ISNA(VLOOKUP(B1,A:A,1,FALSE)),B1,"")

And then filter non-empty cells in column C

Otherwise this simple macro will clear the duplicates in place

Sub FilterDuplicates()
  Dim r As Range
  For Each r In ActiveSheet.Columns("B").Cells
    If r.Value <> "" Then
      On Error Resume Next
      WorksheetFunction.VLookup r, ActiveSheet.Columns("A"), 1, False
      If Err.Number = 0 Then r.ClearContents
      On Error GoTo 0
    End If
  Next r
End Sub

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149297

NON VBA METHOD

Put this formula in Cell C1

=IF(VLOOKUP(B1,A:A,1,0)=B1,"DELETE ME","")

Drag it till the end. and then filter the data on Col C for DELETE ME And then delete the duplicate data.

VBA METHOD

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim delRange As Range, aCell As Range

    Set ws = Sheets("Sheet1")
    With ws
        lRow = .Range("B" & Rows.Count).End(xlUp).Row
        For i = 1 To lRow
            Set aCell = .Columns(1).Find(What:=.Range("B" & i).Value, _
                        LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)

            If Not aCell Is Nothing Then
                If delRange Is Nothing Then
                    Set delRange = .Range("B" & i)
                Else
                    Set delRange = Union(delRange, .Range("B" & i))
                End If
            End If
        Next i
        If Not delRange Is Nothing Then delRange.Delete shift:=xlUp
    End With
End Sub

Upvotes: 0

Jon Crowell
Jon Crowell

Reputation: 22338

This should do what you need. It looks for each value in column B in column A and deletes the cell if it finds a match. Run the code after you've pasted your data into column B. Note that it doesn't remove duplicates from column B, it just removes any values from column B that are in column A. To remove dupes from column B, select the column and choose Remove Duplicates from the Data tab.

You'll need to add a module to the workbook and insert the following code in the module:

code:

Option Explicit

Sub RemoveMatchesFromColumn()
    On Error Resume Next

    Dim LastRow As Long
    Dim SearchText As String
    Dim MatchFound As String

    LastRow = Range("b" & ActiveSheet.Rows.Count).End(xlUp).Row
    SearchText = Range("b" & LastRow).Value

    Do Until LastRow = 0

    MatchFound = Find_Range(SearchText, Columns("A")).Value
        If SearchText = MatchFound Then
            Range("b" & LastRow).Delete Shift:=xlUp
        End If
        LastRow = LastRow - 1
        SearchText = Range("b" & LastRow).Value
    Loop

End Sub


Function Find_Range(Find_Item As Variant, _
    Search_Range As Range, _
    Optional LookIn As Variant, _
    Optional LookAt As Variant, _
    Optional MatchCase As Boolean) As Range

     ' Function written by Aaron Blood
     ' http://www.ozgrid.com/forum/showthread.php?t=27240

    Dim c As Range
    Dim firstAddress As Variant
    If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False

    With Search_Range
        Set c = .Find( _
        What:=Find_Item, _
        LookIn:=LookIn, _
        LookAt:=LookAt, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=MatchCase, _
        SearchFormat:=False)
        If Not c Is Nothing Then
            Set Find_Range = c
            firstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

End Function

Run the sub RemoveMatchesFromColumn. You can step into it to see what it's doing F8 or run it with F5.

Upvotes: 0

Related Questions