Remove duplicate rows

richiwatts

Board Regular
Joined
Aug 27, 2002
Messages
131
I have 3000 rows in my database and 2000 of them are now duplicate rows. how can i get rid of the duplicate rows?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
DATA

FILTER

ADVANCED FILTER

leave CRITERIA blank

check the COPY TO A NEW LOCATION

check the UNIQUE RECORDS ONLY
 

richiwatts

Board Regular
Joined
Aug 27, 2002
Messages
131
Still having a bit of a problem

When I use the advanced filter it gets rid of the duplcate rows, but, some of the rows use more columns than others. After I have pasted the data from the advanced filter it only pastes the first two columns. Some of the rows use up to 5 columns. Why is it doing this and how can i get round it?
 

WJReid

Active Member
Joined
Jul 26, 2002
Messages
317
Hi Richiwatts,

Copy the headings from the original columns ot the new location then do the advanced filter. If you have copied the headings to say H1:L1, then in the copy to range of the advanced filter dialogue box type H1:L1. this should copy all of the columns across.

Regards,

Bill
 

richiwatts

Board Regular
Joined
Aug 27, 2002
Messages
131

ADVERTISEMENT

This is really driving me crazy.

I probably could have done it manually with all the time I have spent trying to do it.

Anyway, this is what I have in the advanced filter.

list range :
sheet1!$A$1:$G$3288
sheet1!$A$3289:$G$3289

When it doesn't crash it removes the duplicates but still only gives me the first two columns. What am I doing wrong. I am getting despirate!!
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
Rich,

Have you got this working yet?

_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-09-04 07:15
 

richiwatts

Board Regular
Joined
Aug 27, 2002
Messages
131
i did it manually in the end and it took for ever.

Thanks anyway for the help you gave

Richi
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550
This will do the trick, if the column you want is the farthest left and is sorted.

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

Takes a little while. But I did one with 16,000 rows last Friday - and it took about 4 minutes to run. Far better than by hand, though.
 

Forum statistics

Threads
1,147,688
Messages
5,742,629
Members
423,745
Latest member
rtaylor1987

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
Top