why does my boss want this done?!!

[duckfoot]

I'm not sure if it can be easily done but I have a column 20000 or so rows long and have been asked to tidy it up. I have repeated data throughout the column and so (when i sort it) may have, say: 5 rows of xxxx, 16 rows of yyyy and then 1 row of zzzz, etc... I need to know how to quickly delete the repeated entries leaving just the first cell of a series of identical entries. Maybe a macro, or some lind of function... Please help. I can't go through it manually, it'll take a week!!!

[memicol]

What about Avanced Filter > Unique Records Only ?

[duckfoot]

thanks for replying but unfortunately it doesn't work... i actually have 4 columns and need to simply remove repetitions in the first leaving a blank cell:
T25400.....buxton ltd
...................buxton ltd
...................buxton ltd
X73900.....AMCT
...................AMCT
T76399.....ATS
I'm not sure if its possible to be honest so never mind...
thanks anyhow

[ This Message was edited by: duckfoot on 2002-04-22 06:53 ]

[ This Message was edited by: duckfoot on 2002-04-22 06:53 ]

[ This Message was edited by: duckfoot on 2002-04-22 06:54 ]

[ This Message was edited by: duckfoot on 2002-04-22 06:54 ]

[ This Message was edited by: duckfoot on 2002-04-22 06:54 ]

[memicol]

Try this (assumes you data is in columns A:D) :-

Columns(1).Insert
With Range([B2], [B65536].End(xlUp)).Offset(0, -1)
.FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).Offset(0, 1).ClearContents
.EntireColumn.Delete
End With

[Mark W.]

Does you boss actually need the duplicates deleted or simply not displayed so the listing is easier to read?

You could apply a conditional format using the formula, =A2=A1, that applies a font color that matches the cell background pattern.

Deleting such duplicate entries makes the list unusable for AutoFilters and PivotTables.

[ This Message was edited by: Mark W. on 2002-04-22 07:21 ]

[shades]

Someone posted this on another site. It works well, even though the loop takes a while. Since I am not a programmer, I haven't fine-tuned it. It works.

Note: The item checking for duplicates is in column A.

------

Sub delete_duplicates()
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Cells(rowx, 1).Value) = UCase(Cells(rowx + 1, 1).Value) Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub

[Amds]

On 2002-04-22 11:36, shades wrote:
Someone posted this on another site. It works well, even though the loop takes a while. Since I am not a programmer, I haven't fine-tuned it. It works.

Note: The item checking for duplicates is in column A.

------

Sub delete_duplicates()
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Cells(rowx, 1).Value) = UCase(Cells(rowx + 1, 1).Value) Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub

But Duckfoot wants to clear the contents of the duplicate cells in column A, not delete the entire row.
In any event, the code posted by "memicol" is more efficient.
Amended to include error handler :-

Application.Screenupdating=false
Columns(1).Insert
With Range([B2], [B65536].End(xlUp)).Offset(0, -1)
.FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],1,"""")"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 1).Offset(0, 1).ClearContents
On Error GoTo 0
.EntireColumn.Delete
End With


Note : Code to do the same thing could be created with the macro recorder (no VBA knowledge required).
Also, if it is only a one-off requirement, just do it manually without recording the macro.

[duckfoot]

ok ok... thanks everyone for your help, but i can't quite get it working.
Do i simply need to copy the code suggested by Memicol and refined by AMDS as the code of a macro? If so, how come it finishes: "ends with" instead of "end sub"? Sorry if i'm being stupid...

[duckfoot]

ok ok... thanks everyone for your help, but i can't quite get it working.
Do i simply need to copy the code suggested by Memicol and refined by AMDS as the code of a macro? If so, how come it finishes: "ends with" instead of "end sub"? Sorry if i'm being stupid...

[Asala42]

Hiya,

I see a lot of coding for this question but if you have the data sorted already, why not use a quick formula?

In an inserted blank column (starting with say A2): =if(B2=B1,"",B2)

Then copy/paste it over as values, then delete or hide the original column.

Adam