taking a long list of duplicates...

[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

 

[Per Jessen]

Hi

Look at the Filter function.

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

Regards,
Per

 

[T. Valko]

Use the advanced filter to copy the uniques to another location.

If you use Excel 2007 it can remove duplicates in place.

 

[Bob Phillips]

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

 

[roadkill]

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