# How do I Delete Duplicates but with Unique Endings?

#### Helios9

##### Board Regular
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!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### QuietRiot

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

Code:
``````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

##### MrExcel MVP
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

##### Board Regular
Thank You!

That's awesome! Thanks for the help!

#### QuietRiot

##### Well-known Member
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

Replies
0
Views
197
Replies
7
Views
171
Replies
1
Views
441
Replies
3
Views
106
Replies
11
Views
2K

1,181,420
Messages
5,929,801
Members
436,696
Latest member
Mr Rice

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back