Removing all duplicate records except one copy

**adam a** · 07-03-2005, 07:54 AM

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** · 07-03-2005, 08:14 AM

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

**woodlot4** · 08-30-2005, 12:53 AM

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

Originally Posted by 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

**transparencia** · 05-16-2011, 10:14 AM

This was never solved, was it?

So wow can I keep one row of a group of duplicates?

Example, the function deletes the red rows:

Row 1___1000____________20
Row 2___1000 ___________ 20
Row 3___1000 ___________ 20

Dave Peterson · 07-03-2005, 09:05 AM

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

adam a wrote:
>
> 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
>
> --
> adam a
> ------------------------------------------------------------------------
> adam a's Profile: http://www.excelforum.com/member.php...o&userid=24873
> View this thread: http://www.excelforum.com/showthread...hreadid=384161

--

Dave Peterson

Dave Peterson · 07-03-2005, 02:05 PM

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

sorry.

Dave Peterson wrote:
>
> What happens if you select column A first (or make the list range just column
> A)?
>
> adam a wrote:
> >
> > 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
> >
> > --
> > adam a
> > ------------------------------------------------------------------------
> > adam a's Profile: http://www.excelforum.com/member.php...o&userid=24873
> > View this thread: http://www.excelforum.com/showthread...hreadid=384161
>
> --
>
> Dave Peterson

--

Dave Peterson

R.VENKATARAMAN · 07-03-2005, 09:05 AM

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

adam a <adam.a.1rlema_1120392301.8427@excelforum-nospam.com> wrote in
message news:adam.a.1rlema_1120392301.8427@excelforum-nospam.com...
>
> 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
>
>
> --
> adam a
> ------------------------------------------------------------------------
> adam a's Profile:
http://www.excelforum.com/member.php...o&userid=24873
> View this thread: http://www.excelforum.com/showthread...hreadid=384161
>

Jim May · 07-03-2005, 10:05 AM

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

"adam a" <adam.a.1rlema_1120392301.8427@excelforum-nospam.com> wrote in
message news:adam.a.1rlema_1120392301.8427@excelforum-nospam.com...
>
> 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
>
>
> --
> adam a
> ------------------------------------------------------------------------
> adam a's Profile:
> http://www.excelforum.com/member.php...o&userid=24873
> View this thread: http://www.excelforum.com/showthread...hreadid=384161
>

Anne Troy · 07-03-2005, 10:05 AM

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

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

www.OfficeArticles.com
www.MyExpertsOnline.com

"adam a" <adam.a.1rlema_1120392301.8427@excelforum-nospam.com> wrote in
message news:adam.a.1rlema_1120392301.8427@excelforum-nospam.com...
>
> 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
>
>
> --
> adam a
> ------------------------------------------------------------------------
> adam a's Profile:
http://www.excelforum.com/member.php...o&userid=24873
> View this thread: http://www.excelforum.com/showthread...hreadid=384161
>

Tom Ogilvy · 07-03-2005, 01:05 PM

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

--
Regards,
Tom Ogilvy

"adam a" <adam.a.1rlema_1120392301.8427@excelforum-nospam.com> wrote in
message news:adam.a.1rlema_1120392301.8427@excelforum-nospam.com...
>
> 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
>
>
> --
> adam a
> ------------------------------------------------------------------------
> adam a's Profile:
http://www.excelforum.com/member.php...o&userid=24873
> View this thread: http://www.excelforum.com/showthread...hreadid=384161
>