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.

.