Help - How do I Remove Duplicates

OzPanda

New Member
Joined
Mar 1, 2012
Messages
19
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
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
 
Upvote 0
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.
Rich (BB 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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top