How do I Delete Duplicates but with Unique Endings?

[Helios9]

Hello Forum,
Thanks in advance for any help that may be offered and sorry if this question has already been addressed.

I have a column of data. Many cells have duplicate content (of varying length) up to the first seven or eight digits but thereon have unique characters. For example:

DV-302_3
DV-302_6
DV-302_kl
DV-305_m6
GH-4587_fdt_2
GH-4587_jgk_9
GH-4587_aqm_c
GH-4588_fdt_565
GH-4588_djj_5
GH-4589_rdc_1
GH-4589_fpp_7
GH-4589_sge_u

My goal is to delete all duplicates based on the first six or seven digits only, so I'm left with only one DV-302, one GH-4587, one GH-4588 etc. In other words, if the first seven digits are duplicated, the whole 'duplicate' cell is deleted.

(I don't need to keep the unique characters beyond the first seven.)


Thanks again for any help!

 

[QuietRiot]

does this work for you? items have to be in column A it inserts a column 'B' then deletes duplicates

Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],7)"
    Selection.AutoFill Destination:=Range("B1:B6001"), Type:=xlFillDefault
        lastrow = Range("B65536").End(xlUp).Row
    Calculate
    For x = lastrow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("B1:B" & x), Range("B" & x).Text) > 1 Then
            Range("B" & x).EntireRow.Delete
        End If
    Next x
End Sub

 

[tusharm]

Suppose your data are in A starting with A2. Then, in B2 enter the formula =LEFT(A2,SEARCH("_",A2)-1) and copy down as needed. This will give you the 6 or 7 characters of interest to you. Next, search Chip Pearson's site www.cpearson.com for several different ways of deleting duplicates.

 

[Helios9]

Thank You!

That's awesome! Thanks for the help!

 

[QuietRiot]

it doesnt matter if its 6 or 7..

just use the max 7 cause either way itll be duplicate

ie;
DV-302_
DV-302_
DV-302_
DV-305_
GH-4587
GH-4587
GH-4587


so i just used 7