Prev: How do I match 3 different workbooks information for similar data?
Next: need help with formula
From: Donna on 18 Apr 2010 20:41
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

From: Dave Peterson on 18 Apr 2010 21:39
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
From: T. Valko on 18 Apr 2010 21:48
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" <donna(a)yahoo.com> wrote in message
news:463A679D-7786-49D8-A2B4-AE7BCB86F82C(a)microsoft.com...
> 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
>


From: Steve Dunn on 19 Apr 2010 13:46
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" <donna(a)yahoo.com> wrote in message
news:463A679D-7786-49D8-A2B4-AE7BCB86F82C(a)microsoft.com...
> 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
>

From: Donna on 19 Apr 2010 17:04
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" <donna(a)yahoo.com> wrote in message
> news:463A679D-7786-49D8-A2B4-AE7BCB86F82C(a)microsoft.com...
> > 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
> >
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: How do I match 3 different workbooks information for similar data?
Next: need help with formula