A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Finding duplicate addresses



 
 
Thread Tools Display Modes
  #1  
Old April 19th, 2010, 01:41 AM posted to microsoft.public.excel.worksheet.functions
Donna[_5_]
external usenet poster
 
Posts: 69
Default Finding duplicate addresses

If you could help me with this it would be greatly appreciated.
Column A Has client codes
Column B Has client names
Column C Has client addresses
The same address may be used for multiple client codes. I am only looking
for duplicate addresses. What I would like to end up with in my report is
only the lines that have addresses that are listed more than once. If an
address is only listed once , I do not want it to show on my report.
Thanks

  #2  
Old April 19th, 2010, 02:39 AM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default Finding duplicate addresses

I would add a new column D and concatenate the Code with the address:

(with headers in row 1 and data starting in row 2)

In D2:
=A2&"---"&c2
and drag down the column as far as you need.

Then in E2 (another new column):
=countif(D,d2)
And drag down.

This will count the number of times the code/address on this row is used in your
data.

Apply Data|Filter|Autofilter to column E.
Show the values that are greater than 1

If you need the info on a new sheet, copy the visible cells to a new sheet.
(I'd just keep them filtered and out of the way.)



Donna wrote:

If you could help me with this it would be greatly appreciated.
Column A Has client codes
Column B Has client names
Column C Has client addresses
The same address may be used for multiple client codes. I am only looking
for duplicate addresses. What I would like to end up with in my report is
only the lines that have addresses that are listed more than once. If an
address is only listed once , I do not want it to show on my report.
Thanks


--

Dave Peterson
  #3  
Old April 19th, 2010, 02:48 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Finding duplicate addresses

One way...

Use a helper column to mark the duplicates.

Enter this formula in column D and copy down to the end of data:

=IF(COUNTIF(C$2:C$20,C2)1,"DUP","")

Then apply AutoFilter and filter on column D = DUP

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
If you could help me with this it would be greatly appreciated.
Column A Has client codes
Column B Has client names
Column C Has client addresses
The same address may be used for multiple client codes. I am only
looking
for duplicate addresses. What I would like to end up with in my report is
only the lines that have addresses that are listed more than once. If
an
address is only listed once , I do not want it to show on my report.
Thanks



  #4  
Old April 19th, 2010, 06:46 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Finding duplicate addresses

Hi Donna, try this.

In E2:

=INDEX($C$2:$C$100,MATCH(TRUE,
INDEX(COUNTIF($C$2:$C$100,$C$2:$C$100)1,),0))

in E3:

=INDEX($C$2:$C$100,MATCH(1,
INDEX((COUNTIF(E$2:$E2,$C$2:$C$100)=0)*
(COUNTIF($C$2:$C$100,$C$2:$C$100)1),),0))

copy E3 down as far as required.

HTH
Steve.



"Donna" wrote in message
...
If you could help me with this it would be greatly appreciated.
Column A Has client codes
Column B Has client names
Column C Has client addresses
The same address may be used for multiple client codes. I am only
looking
for duplicate addresses. What I would like to end up with in my report is
only the lines that have addresses that are listed more than once. If
an
address is only listed once , I do not want it to show on my report.
Thanks


  #5  
Old April 19th, 2010, 10:04 PM posted to microsoft.public.excel.worksheet.functions
Donna[_5_]
external usenet poster
 
Posts: 69
Default Finding duplicate addresses

Worked great, thanks

"T. Valko" wrote:

One way...

Use a helper column to mark the duplicates.

Enter this formula in column D and copy down to the end of data:

=IF(COUNTIF(C$2:C$20,C2)1,"DUP","")

Then apply AutoFilter and filter on column D = DUP

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
If you could help me with this it would be greatly appreciated.
Column A Has client codes
Column B Has client names
Column C Has client addresses
The same address may be used for multiple client codes. I am only
looking
for duplicate addresses. What I would like to end up with in my report is
only the lines that have addresses that are listed more than once. If
an
address is only listed once , I do not want it to show on my report.
Thanks



.

  #6  
Old April 19th, 2010, 10:44 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Finding duplicate addresses

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
Worked great, thanks

"T. Valko" wrote:

One way...

Use a helper column to mark the duplicates.

Enter this formula in column D and copy down to the end of data:

=IF(COUNTIF(C$2:C$20,C2)1,"DUP","")

Then apply AutoFilter and filter on column D = DUP

--
Biff
Microsoft Excel MVP


"Donna" wrote in message
...
If you could help me with this it would be greatly appreciated.
Column A Has client codes
Column B Has client names
Column C Has client addresses
The same address may be used for multiple client codes. I am only
looking
for duplicate addresses. What I would like to end up with in my report
is
only the lines that have addresses that are listed more than once.
If
an
address is only listed once , I do not want it to show on my report.
Thanks



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.