why does my boss want this done?!!

duckfoot

New Member
Joined
Apr 21, 2002
Messages
22
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!!!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top