Removing Duplcate Names
Hello,
I have a spreadsheet that contains just over 7,000 rows; this spreadsheet contains an account number and all the people attached to that account including the role they play on the account. For example; Acct # 123456 can belong to a company who is the Tax Owner but the account can also have additional people attached to it who may have multilpe roles such as Trustee, Non-Tax Signor, Guantor, etc... The company and people can and most often do have different addresses. I need to do a mailing using this spreadsheet but since the account can have companies/people with many roles I end up with multiple rows containing the same account numbers, names, and addresses but the role on the account will make each row unique because the roles are different. I need help in creating a formula or VBA code that will remove any uneeded rows leaving only a single entry for each name and address. If I had this: Acct Name Addr Role 123 XYZ Co. 123 Main St. Anytown NJ TaxOwner 123 John Doe 111 This St Thattown NJ Guantor 123 John Doe 111 This St Thattown NJ Trustee 123 Bob Jones 456 6th Ave Histown NJ Trustee 123 Bob Jones 456 6th Ave Histown NJ Guantor How can I end up with this?: Acct Name Addr Role 123 XYZ Co. 123 Main St. Anytown NJ Tax Owner 123 John Doe 111 This St Thattown NJ Guantor 123 Bob Jones 456 6th Ave Histown NJ Guantor I don't care which role remains, I ony care about making sure that I have 1 entry for each name and address attached to each account. I appreciate your reading this long post and any help or suggestions that are provided. Thank you, Dave Y |
http://www.cpearson.com/excel/deleti...eDuplicateRows
-- Kind Regards, Niek Otten Microsoft MVP - Excel "Dave Y" wrote in message ... Hello, I have a spreadsheet that contains just over 7,000 rows; this spreadsheet contains an account number and all the people attached to that account including the role they play on the account. For example; Acct # 123456 can belong to a company who is the Tax Owner but the account can also have additional people attached to it who may have multilpe roles such as Trustee, Non-Tax Signor, Guantor, etc... The company and people can and most often do have different addresses. I need to do a mailing using this spreadsheet but since the account can have companies/people with many roles I end up with multiple rows containing the same account numbers, names, and addresses but the role on the account will make each row unique because the roles are different. I need help in creating a formula or VBA code that will remove any uneeded rows leaving only a single entry for each name and address. If I had this: Acct Name Addr Role 123 XYZ Co. 123 Main St. Anytown NJ TaxOwner 123 John Doe 111 This St Thattown NJ Guantor 123 John Doe 111 This St Thattown NJ Trustee 123 Bob Jones 456 6th Ave Histown NJ Trustee 123 Bob Jones 456 6th Ave Histown NJ Guantor How can I end up with this?: Acct Name Addr Role 123 XYZ Co. 123 Main St. Anytown NJ Tax Owner 123 John Doe 111 This St Thattown NJ Guantor 123 Bob Jones 456 6th Ave Histown NJ Guantor I don't care which role remains, I ony care about making sure that I have 1 entry for each name and address attached to each account. I appreciate your reading this long post and any help or suggestions that are provided. Thank you, Dave Y |
You may introduce a formula like the one below in a new column.
X3=AND(A3=A2,B3=B2) Copy the formula to all rows. This is to be applied if your data is sorted one. Assuming the formula is applied in Cell X3. If returns TRUE it means, row 2 and row 3 are duplicates. If returns false, it is not duplicate. Delete all the rows containing true value. |
Hi Niek,
Thank you for the link. The code worked perfectly. I appreciate it. Take care, Dave Y -----Original Message----- http://www.cpearson.com/excel/deleti...eleteDuplicate Rows -- Kind Regards, Niek Otten Microsoft MVP - Excel "Dave Y" wrote in message ... Hello, I have a spreadsheet that contains just over 7,000 rows; this spreadsheet contains an account number and all the people attached to that account including the role they play on the account. For example; Acct # 123456 can belong to a company who is the Tax Owner but the account can also have additional people attached to it who may have multilpe roles such as Trustee, Non-Tax Signor, Guantor, etc... The company and people can and most often do have different addresses. I need to do a mailing using this spreadsheet but since the account can have companies/people with many roles I end up with multiple rows containing the same account numbers, names, and addresses but the role on the account will make each row unique because the roles are different. I need help in creating a formula or VBA code that will remove any uneeded rows leaving only a single entry for each name and address. If I had this: Acct Name Addr Role 123 XYZ Co. 123 Main St. Anytown NJ TaxOwner 123 John Doe 111 This St Thattown NJ Guantor 123 John Doe 111 This St Thattown NJ Trustee 123 Bob Jones 456 6th Ave Histown NJ Trustee 123 Bob Jones 456 6th Ave Histown NJ Guantor How can I end up with this?: Acct Name Addr Role 123 XYZ Co. 123 Main St. Anytown NJ Tax Owner 123 John Doe 111 This St Thattown NJ Guantor 123 Bob Jones 456 6th Ave Histown NJ Guantor I don't care which role remains, I ony care about making sure that I have 1 entry for each name and address attached to each account. I appreciate your reading this long post and any help or suggestions that are provided. Thank you, Dave Y . |
Hi T G,
Thank you for the formula. The gentleman in the previuos post provided a link that gave me code that did the job for me. But I will keep this formula for possible future use. I appreciate your reply. Take care, Dave Y -----Original Message----- You may introduce a formula like the one below in a new column. X3=AND(A3=A2,B3=B2) Copy the formula to all rows. This is to be applied if your data is sorted one. Assuming the formula is applied in Cell X3. If returns TRUE it means, row 2 and row 3 are duplicates. If returns false, it is not duplicate. Delete all the rows containing true value. . |
All times are GMT +1. The time now is 12:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com