Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 8th 05, 05:00 PM
Dave Y
 
Posts: n/a
Default 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

  #2   Report Post  
Old April 8th 05, 05:13 PM
Niek Otten
 
Posts: n/a
Default

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



  #3   Report Post  
Old April 8th 05, 06:11 PM
T G Sekhar
 
Posts: n/a
Default

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.

  #4   Report Post  
Old April 8th 05, 06:17 PM
 
Posts: n/a
Default

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



.

  #5   Report Post  
Old April 8th 05, 06:26 PM
 
Posts: n/a
Default

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.

.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sorting names alphabetically when names may start with numbers John Smith Excel Discussion (Misc queries) 3 May 11th 05 08:06 PM
Assigning names to a spreadsheet that updates Chance New Users to Excel 3 April 7th 05 03:43 AM
Tab Names II JohnUK Excel Discussion (Misc queries) 1 March 7th 05 03:00 PM
Pull unique names for drop down list [email protected] Excel Discussion (Misc queries) 3 February 1st 05 10:23 PM
How can I find the common names in two columns of names? hako Excel Discussion (Misc queries) 2 December 8th 04 01:59 AM


All times are GMT +1. The time now is 11:19 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017