Help - How do I Remove Duplicates

[OzPanda]

Hi Master Excel Users.

I have a two dimensional cell range of cells say from A1 to P5000

I would like to remove all duplicates within this range but keep the uniques where they currently are sitting, in their current position.

Any good way of doing that? Alot of cells are blank within this block of cells.

The solution I am thinking is, i create a second tab and I some how search the text within the first tab and return the position of the first instance of where that text is found. If that position is the same position as that cell then return that text (as it's the first instance), if not return blank as it means this is the second or third etc (not first) occurrence.

However I have reached an impasse as MATCH can only do one dimensional right?

the other solution i had in mind is put all the columns into 1 column and do a countif > 1 then don't show but then i have put the results back into a grid... :\

another solution i had is use the conditional formatting to shade in the duplicates but then i don't know how to remove those after they are shaded.

Any advice would be helpful. Cheers

 

[Michael M]

Maybe this from the web...

1. Click the Advanced Filter button on the Data tab of the Ribbon.
2. Select the “Copy to another location” radio button.
3. Select the “List range”. The range/column that contains the duplicate values.
4. Select the “Copy to” range. ...
5. Click the “Unique records only” checkbox.
6. Click the OK button.

 

[OzPanda]

Maybe this from the web...

hey it's not working, it's treating the whole row of data as unique

like let's say i have a grid of 3 x 4 cells

cat,bat,mat
cat,bat,gap
cat,bat,mat
cat,cat,sat

your suggested method would return

cat,bat,mat
cat,bat,gap

cat,cat,sat

what we require returned is

cat,bat,mat
,,gap
,,
,,sat

 

[JoeMo]

I think that you are asking for a duplicate removal solution that goes a bit beyond what typical duplicate removal entails.

If the values in your 2-D range of cells are constants (cells don't contain formulas), here's a macro solution you can try. Note: set the range you want checked for duplicates (shown in red in the code below), then run the code.

Sub OzPanda()
Dim R As Range, V As Variant, d As Object, i As Long, j As Long
Set R = Range("A1:C4")  'Enter input data range here
V = R.Value
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(V, 1)
    For j = 1 To UBound(V, 2)
        If Not d.exists(V(i, j)) Then
            d.Add V(i, j), d.Count + 1
        Else
            V(i, j) = ""
        End If
    Next j
Next i
application.screenupdating = False
R.Value = V
application.screenupdating = True
End Sub

 

[OzPanda]

see solution here:
https://stackoverflow.com/questions/35912275/excel-vba-delete-duplicates-keep-positioning

the last one on the page.

it works well but the OP requested to have even the first instance removed... so both pairs of duplicates gets removed.

i need to keep the first original unique one.. grrr hate that OP if he didn't say anything the solution would be there.

 

[OzPanda]

JOE THANK YOU. YOU ARE MY HERO! this works.

 

[JoeMo]

JOE THANK YOU. YOU ARE MY HERO! this works.

You are welcome - thanks for the reply.