|
Removing all duplicate records except one copy
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 |
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 -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=384161 |
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 wrote in message ... 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 |
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 |
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" wrote in message ... 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 |
Hi, Adam. Here are several ways to deal with duplicates...
http://www.officearticles.com/excel/...ft_excel. htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "adam a" wrote in message ... 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 |
Advanced filter should work fine if you select columns A and B when you
apply the filter -- Regards, Tom Ogilvy "adam a" wrote in message ... 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 |
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 |
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 dominicb Wrote: 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 ------------------------------------------------------------------------ woodlot4's Profile: http://www.excelforum.com/member.php...o&userid=26613 View this thread: http://www.excelforum.com/showthread...hreadid=384161 |
| All times are GMT +1. The time now is 08:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com