Remove duplicates function will not work -

[dimleyk]

Hello guys
This is my first post here, but i went over previous posts searching for assistance with the following issue:
I need to remove duplicate values and leave only unique values. Remove duplicates function in Execl2010 is stating that no duplicates were found, although the selected data were correctly identified, and no spaces existed in different cells - i used func. LEN to verify this.
The example data looks like the following:

	Col1	Col2	Col3	Col4	Col5
Row1	a	he's	since	anybod	hed
Row2	about	highly	singl	anyone	he'd
Row3	above	him	six	after	her
Row4	absolutely	himself	so	everybod	hers
Row5	across	his	after	everyone	herself
Row6	actually	hopefully	somebod	everything	hes
Row7	after	how	somehow	hed	his
Row8	again	however	someone	he'd	him
Row9	against	hundred	something	her	again

<tbody>
</tbody>

xlsx version available here: https://dl.dropboxusercontent.com/u/57324527/dupblic1.xlsx

Thank you for all those who are familiar with such questions and are willing to help

Thanks!

<tbody>
</tbody>

 

[patel45]

You are right, I can not remove them on my Excel 2010, ir runs only if I select one column

 

[HaHoBe]

Hi, dimleyk,

I wonder what items should be removed as duplicates as I may only find unique values.

Ciao,
Holger

 

[lolotters]

Hi, dimleyk,

I wonder what items should be removed as duplicates as I may only find unique values.

Ciao,
Holger

There are at least five duplicates in that chart.

To the OP, ensure there are no spaces at the end of your data as Excel will not recognize these as duplicates. Occasionally this will happen to me if I drag or copy data from one cell to another, for some reason it will not recognize it immediately, and I'm forced to manually type it in and then reformat that column or the entire sheet and they will then be highlighted. I don't know if this helps, but it is what I do.

 

[hiker95]

dimleyk,

If this is what your raw data looks like:

Excel 2007
	A	B	C	D	E
1	a	he's	since	anybod	hed
2	about	highly	singl	anyone	he'd
3	above	him	six	after	her
4	absolutely	himself	so	everybod	hers
5	across	his	after	everyone	herself
6	actually	hopefully	somebod	everything	hes
7	after	how	somehow	hed	his
8	again	however	someone	he'd	him
9	against	hundred	something	her	again
10
Sheet1

Is this what the results should look like?

Excel 2007
	A	B	C	D	E
1	a	he's	since	anybod
2	about	highly	singl	anyone
3	above		six
4	absolutely	himself	so	everybod	hers
5	across			everyone	herself
6	actually	hopefully	somebod	everything	hes
7		how	somehow
8		however	someone
9	against	hundred	something
10
Sheet1

 

[hiker95]

dimleyk,

Welcome to the MrExcel forum.

Sample raw data:

Excel 2007
	A	B	C	D	E
1	a	he's	since	anybod	hed
2	about	highly	singl	anyone	he'd
3	above	him	six	after	her
4	absolutely	himself	so	everybod	hers
5	across	his	after	everyone	herself
6	actually	hopefully	somebod	everything	hes
7	after	how	somehow	hed	his
8	again	however	someone	he'd	him
9	against	hundred	something	her	again
10
Sheet1

After the macro:/b]

Excel 2007
	A	B	C	D	E
1	a	he's	since	anybod
2	about	highly	singl	anyone
3	above		six
4	absolutely	himself	so	everybod	hers
5	across			everyone	herself
6	actually	hopefully	somebod	everything	hes
7		how	somehow
8		however	someone
9	against	hundred	something
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RemoveAllDupes()
' hiker95, 01/24/2014, ME751650
Dim lr As Long, lc As Long
Dim rng As Range, c As Range, frng As Range
Dim n As Long, i As Long, h As String
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = Range(Cells(1, 1), Cells(lr, lc))
With rng
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
For Each c In rng
  If c <> "" Then
    n = Application.CountIf(rng, c)
    If n > 1 Then
      h = c
      c = ""
      For i = 2 To n
        Set frng = rng.Find(h, LookAt:=xlWhole)
        If Not frng Is Nothing Then
          Cells(frng.Row, frng.Column) = ""
        End If
      Next i
    End If
  End If
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveAllDupes macro.

 

[HaHoBe]

Hi, loletters,

we as humans understand a different thing about Duplicates as does Excel. In Excel this tool has been present as part of the Advanced Filter ever since introduced. Two things need to stay in memory: the Advanced Filter will only work on single columns to be compared and will always have an effect on the row in which Excel recognizes a duplicate.

The feature which was introduced from Excel2007 on does the same so there really is no way to use this to recognise duplicates in a range or just delete contents for single cells (it will always influence the rows if a comparision on a column found a match). hiker95 provided a macro which does exactly what the tool from Excel can´t deliver.

Ciao,
Holger

 

[hiker95]

HaHoBe,

Even though dimleyk has marked their thread with an "Unhappy" indicator in the title.

hiker95 provided a macro which does exactly what the tool from Excel can´t deliver.

Thanks for that, and, have a great day.