Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding duplicate addresses across 2 worksheets in the same workbo | Excel Worksheet Functions | |||
Need help finding non-duplicate email addresses... | Excel Discussion (Misc queries) | |||
How do you delete duplicate addresses, but keep duplicate names? | Excel Discussion (Misc queries) | |||
finding duplicate addresses | Excel Worksheet Functions | |||
finding addresses | Excel Discussion (Misc queries) |