Duplicates

**mbuckley56** · 02-08-2010, 05:39 PM

I have a table of data like this:

A , 1 , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3 , 3

I need to eliminate duplicate entries WITHIN a row. So, I need the table to become:

A , 1
B , 2 , 1
C , 3 , 4
D , 1 , 3
E , 3

Is there an easy way to do this for a large sample? Also, there are actually more like 8 columns of this data, if thats important. Any help you all could give me would be immensely appreciated. Thank you so much

Michael

**JBeaucaire** · 02-08-2010, 05:41 PM

The only way to actually erase duplicates is with a macro of some sort. Are you OK with VBA?

If so, upload a sample workbook demonstrating the exact data to deal with. Don't oversimplify the sample workbook.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**jj72uk** · 02-08-2010, 05:49 PM

Something alond the lines what JB was saying.

A macro would be the easiest way.

Maybe something like this:

Code:

Sub UniqueSelection() 
    Dim coll As New Collection 
    Dim lcount As Long 
    Dim cell As Range 
     
    On  Error Resume Next 
    For Each cell In Selection 
        coll.Add cell.Value, CStr(cell.Value) 
    Next cell 
    On Error Goto 0 
     
    Selection.ClearContents 
     
    lcount = 0 
    For Each cell In Selection 
        lcount = lcount + 1 
        If lcount > coll.Count Then Exit For 
        cell.Value = coll.Item(lcount) 
    Next cell 
End Sub

All it requires you to do is to highlight what what you want to look for duplicates in and run the macro

**mbuckley56** · 02-08-2010, 06:11 PM

Hey thanks so much.

This looks really good. Except, it only works when I do one row at a time. When I select multiple rows, it doesn't keep the row structure when it puts in the new values. They end up in the wrong row.. (Value from row 3 end up in row 2 or 1, etc.) Is there a quick fix to this?

Thanks so much for all your help.
Michael

**mbuckley56** · 02-08-2010, 06:18 PM

I attached a small sample. All of your help is so appreciated!

Thank you,
Michael

**JBeaucaire** · 02-08-2010, 06:29 PM

So, you want to evaluate each row D:M and remove duplicates within the row, that's it? Squeeze to the left as they are removed?

**mbuckley56** · 02-08-2010, 06:32 PM

Thats exactly right. Is there an easy way to edit this code?

Thanks, Michael

**JBeaucaire** · 02-08-2010, 07:11 PM

Not really easy, but not overly hard either. Try this:

Code:

Option Explicit

Sub RemoveDupesInRows()
'JBeaucaire  2/8/2010
'Remove duplicate values within rows from D:M
Dim RNG As Range, LR As Long, LC As Long, i As Long
Application.ScreenUpdating = False
LR = Range("D" & Rows.Count).End(xlUp).Row

Set RNG = Range("D2:M" & LR)

RNG.Copy
Range("AA2").PasteSpecial xlPasteAll, Transpose:=True
RNG.Clear
LC = Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 27), Cells(1, LC)) = "Key"

For i = 27 To LC
    Columns(i).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z1"), Unique:=True
    Columns(26).Copy
    Columns(i).PasteSpecial xlPasteValues
    Columns(26).Clear
Next i

With Range(Cells(2, 27), Cells(11, LC + 1))
    .SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
    .Copy
    Range("D2").PasteSpecial xlPasteAll, Transpose:=True
    .Offset(-1, 0).Clear
End With

Application.ScreenUpdating = True
End Sub

**mbuckley56** · 02-08-2010, 07:46 PM

This is really awesome.

Just a few really quick questions. Is there any way to make the range selectable? Also, what is the counter from 1 to 27? I just want to make sure I understand this code, can edit it, so I can continue to learn and get better.

Thanks so much for all of your help so far. I can't tell you how grateful I am.

Sincerely,

Michael

**JBeaucaire** · 02-08-2010, 07:55 PM

I hardcoded column D:M as you indicated those were the columns to evaluate.

Selectable? It's already self-adjusting. It will go down column D to the bottom of the data set, how ever far that may be, then remove the duplicates in columns D:M down to that depth.

To keep from having to evaluate each cell one at a time, I decided to use Excel's Advanced Filter. But Adv Filter only works on columns, not rows, so I copied the dataset to column AA (that's column 27) and transposed it into columns. Then I use the Adv Filter for unique values on each column (from 27 to the Last Column (LC) with transposed data in it). When I'm done I transpose the data back into the original position at D2, replacing the data that was there originally.