Removing all duplicate records except one copy

[adam a]

Hello,

I have combined data from several sheets. The data is of product
information that is sold in a chain of shops. I am trying to compile a
master list of all products sold across the chain. In column A is the
product NUMBER, in column B is the supplier number for that product.
e.g.:


________Column A_________Column B

Row 1___1000____________20
Row 2___1000 ___________ 20
Row 3___2000____________30
Row 4___2000____________20
Row 5___3000____________30
Row 6___3000____________30
Row 7___3000____________20

REQUIRED RESULT:

________Column A_________Column B

Row 1___1000____________20
Row 2___2000____________30
Row 3___2000____________20
Row 4___3000____________30
Row 5___3000____________20


n.b. there are other columns of dat but I want the comparison for
duplicates to ignore them (if possible). I have tried using ADVANCED
FILTERS but this gets rid of ALL records that are duplicate and keeps
unique ones only!

Any help is a lifesaver as project deadline for FRIDAY 8th JULY!!!!!

Regards

Adam

 

[dominicb]

Good afternoon Adam a

The code listed below will do the trick for you. It will hide all rows
containing duplicate information, so you don't lose the information - it
just removes it from view. However feel free to alter it to delete the
rows completely if you wish.

Sub HideDuplicate()
On Error Resume Next
Set UsrRng = Selection
For Each UsrCel In UsrRng
Dupd = UsrCel.Address
Dupd = UsrRng.Find(What:=UsrCel, After:=UsrCel, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Address
If Dupd <> UsrCel.Address Then UsrCel.EntireRow.Hidden = True
Next UsrCel
End Sub


To use this highlight just one column that contains the duplicate
information and then run the macro.

HTH

DominicB

 

[R.VENKATARAMAN]

introduce a top row as header row
use data=filter=advancefilter -unique values

the creiteria range may be left blank or the same as database

 

[Dave Peterson]

What happens if you select column A first (or make the list range just column
A)?

 

[Jim May]

I'd back up file first;
then Insert a "helper-column" to left of ColA
in new A1 enter =B1&C1 << to concatenate,
then Copy A1 down to A2000 (?);
Then do the Advance Filter option again checking the Unique Records option
(based on Col A).
HTH

 

[Anne Troy]

Hi, Adam. Here are several ways to deal with duplicates...
http://www.officearticles.com/excel/dealing_with_duplicate_records_in_microsoft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Tom Ogilvy]

Advanced filter should work fine if you select columns A and B when you
apply the filter

 

[Dave Peterson]

Oops. I didn't see the column B stuff.

sorry.

What happens if you select column A first (or make the list range just column
A)?

 

[woodlot4]

I think this is what I have been searching for. I need to delet
duplicate addresses out of a list of thousands. This sounds like i
will work but I have no clue how to write and then apply a macro to d
it. How do I take the details below and apply it to my workbook
woodlot4atyahoo.com