How do I Delete Duplicates but with Unique Endings?

Helios9

Board Regular
Joined
Oct 15, 2006
Messages
127
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!


:LOL:
 

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
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
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
Joined
May 28, 2002
Messages
11,029
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.
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top