Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 76
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
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: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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 76
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
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



.



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
finding duplicate addresses across 2 worksheets in the same workbo DAY Excel Worksheet Functions 2 February 13th 10 01:32 AM
Need help finding non-duplicate email addresses... Greg Excel Discussion (Misc queries) 16 December 19th 07 12:51 PM
How do you delete duplicate addresses, but keep duplicate names? Shelly Excel Discussion (Misc queries) 1 August 28th 06 10:36 PM
finding duplicate addresses diamonds Excel Worksheet Functions 2 June 21st 06 11:40 PM
finding addresses donone Excel Discussion (Misc queries) 5 September 17th 05 07:45 AM


All times are GMT +1. The time now is 07:56 AM.

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

About Us

"It's about Microsoft Excel"