Results 1 to 2 of 2
Thread: Excel Deleting Doubles
2011-07-29, 08:12 #1
- Join Date
- Jul 2011
- 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("D3" & last_row).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Delete the matches
'Combine the remaining lists
ActiveSheet.Sort.SortFields.Add Key:=Range("D2" & last_row), SortOn:=xlSortOnValues, _
.SetRange Range("A1" & last_row)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
2011-07-29, 11:02 #2
- Join Date
- Dec 2000
- New York, NY
- Thanked 29 Times in 27 Posts
Locking thread - see active thread in Visual Basic for Apps forum.