Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2011
    Location
    Cincinnati
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Deleting Doubles

    I have a copy of an old list and a new list. I've got vba script combining them into one list, sorting them by name, and assigning a function down the column near this list. My goal is to identify cells with the same value as another (there will be a maximum of two of each value) with one character and to delete all rows with that character, so I can see what changed between each list.

    I currently put =MATCH(B2, B:B, 0) in the D column and copy it for as far as the combined list goes, but that gives me another list of doubles (the first cell the value is found in). What function would assign a character when it found a double?

    The relative part of my vba script:
    'Match Up the New and Old
    last_row = Application.WorksheetFunction.CountA(Range("B:B"))
    ActiveSheet.Range("D2").Value = "=IFERROR(MATCH(B2, B:B, 0),-1)"
    ActiveSheet.Range("D2").Copy
    ActiveSheet.Range("D3" & last_row).Select
    ActiveSheet.Paste
    ActiveSheet.Range("D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    'Delete the matches
    'Combine the remaining lists

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("D2" & last_row), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
    .SetRange Range("A1" & last_row)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .Apply
    End With

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    Locking thread - see active thread in Visual Basic for Apps forum.

    Gary

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •