taking a long list of duplicates...

  • Thread starter Thread starter DFrank
  • Start date Start date
D

DFrank

....and reducing it to a small list with no space.

I have:

1" Shaft Liner DensGlass Ultra 2'x12'
1" Shaft Liner DensGlass Ultra 2'x12'
1/2" 4'x12' Regular
1/2" 4'x12' Regular
1/2" DensArmor Plus 4'x08' (paperless)
1/2" DensArmor Plus 4'x08' (paperless)
1/2" DensArmor Plus 4'x08' (paperless)
1/2" DensShield 32"x05'
1/2" DensShield 32"x05'
1/2" Hardi-Backer 3'x05'
1/2" Hardi-Backer 3'x05'
1/2" MR 4'x08'
1/2" MR 4'x08'
1/2" MR 4'x08'
5/8" 4'x14' FC Type X
5/8" 4'x14' FC Type X
5/8" 4'x14' FC Type X



and what i want is a list that looks like:

1" Shaft Liner DensGlass Ultra 2'x12'
1/2" 4'x12' Regular
1/2" DensArmor Plus 4'x08' (paperless)
1/2" DensShield 32"x05'
1/2" Hardi-Backer 3'x05'
1/2" MR 4'x08'
5/8" 4'x14' FC Type X


How do i achieve this? Thanks
 
Hi

Look at the Filter function.

Goto Data > Filter > Advanced filter > Check "Unique records only"

Regards,
Per
 
Use the advanced filter to copy the uniques to another location.

If you use Excel 2007 it can remove duplicates in place.
 
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1,
TEST_COLUMN).Value Then

.Rows(i).Delete
End If
Next i

End With

End Sub
 
Short of writing a macro to do it I'd recommend something like this:

Assume your list starts in cell C1.
- in cell D1 (or some other available column), put "=if(C1=C2,"X","")"
- copy this down to the bottom of your list (e.g. to D486)
- select cells D1 through (in this case) D486 and copy
- paste special / values right over the top of the formulas you put in D1 to
D486
- sort by column D to get all of the "X's" together
- delete the group of rows containing "X's"
- resort the list by column C
- delete the nulls ("") in column D

That should do it.

Will
 
Back
Top