Remove multiple references

P

printerdan

I have a song list I need to purge down such as -

Abba Chiquita SFG058
Abba Duck & Run SC3244
Abba Dancing Queen DK041
Abba Dancing Queen SFABBA001
Abba Dancing Queen SC8765
Abba Take A Chance On Me CBEP74

where Dancing Queen is repeated with a different disk code, I want to delete
further references (row D3 thru D4). The intent is to import the results into
a publishing program (InDesign).
 
S

Sheeloo

Assuming you have song names (like Dancing Queen) in Col A and you want to
retain the first row with that name only then do this;
IMP: Do make a copy before trying this

1. Enter this in D1 or first row of the first unused col
= 1 + SUMPRODUCT(--($A$1:$A$100=A1)) - SUMPRODUCT(--(A1:$A$100=A1))
2. Change 100 to the last row in your data set
3. Copy it down to the last row in your data set
4. Filter on ' not equal' to 1 for the col with this formula as it will
enter 1 against first occurrence of a song name
5. Delete the visible rows
 
J

JMay

Sub tester()
lrow = Range("B" & Rows.Count).End(xlUp).Row
For i = lrow To 2 Step -1
Cells(i, 2).Select
If ActiveCell.Value <> ActiveCell.Offset(-1).Value Then
Else
ActiveCell.ClearContents
End If
Next i
End Sub
 
Top