Prev: how do I get rid of ###### that appear instead of text in excel?
Next: Excel On-line
From: KErin on 31 Mar 2010 13:47
Hi,
I have a 1500-row mailing list that includes the guests' account numbers in
one of the columns. Some guests have multiple addresses (work, home, etc) so
for that guest, there are multiple rows but the same account number.
Is there a way to sort and/or filter that will identify all of the multiple
account numbers without having to go through the entire list by hand?
I tried advance filter for unique records, hoping that if I pulled out all
of the people who didn't have multiple addresses it would at least help
narrow the records down, but nothing happened at all when I used that filter.

Thanks for any help you can provide!
From: Huber57 on 31 Mar 2010 13:57
KErin,

You can 'tag' all of the rows with duplicate data. if the account number is
in Col D., insert a column and insert this formula there (assuming your data
starts in the 2nd row):
=IF(COUNTIF(D$2:D$1000,D2)>1,”Duplicated”,"Not a Duplicate")

Then sort on the 'duplicate' column to find all of the duplicate acct numbers.

hope that helps.

"KErin" wrote:

> Hi,
> I have a 1500-row mailing list that includes the guests' account numbers in
> one of the columns. Some guests have multiple addresses (work, home, etc) so
> for that guest, there are multiple rows but the same account number.
> Is there a way to sort and/or filter that will identify all of the multiple
> account numbers without having to go through the entire list by hand?
> I tried advance filter for unique records, hoping that if I pulled out all
> of the people who didn't have multiple addresses it would at least help
> narrow the records down, but nothing happened at all when I used that filter.
>
> Thanks for any help you can provide!
From: KErin on 31 Mar 2010 14:51
WOW! Learn something new every day! Thanks so much, that works & is a
tremendous help!

"Huber57" wrote:

> KErin,
>
> You can 'tag' all of the rows with duplicate data. if the account number is
> in Col D., insert a column and insert this formula there (assuming your data
> starts in the 2nd row):
> =IF(COUNTIF(D$2:D$1000,D2)>1,”Duplicated”,"Not a Duplicate")
>
> Then sort on the 'duplicate' column to find all of the duplicate acct numbers.
>
> hope that helps.
>
> "KErin" wrote:
>
> > Hi,
> > I have a 1500-row mailing list that includes the guests' account numbers in
> > one of the columns. Some guests have multiple addresses (work, home, etc) so
> > for that guest, there are multiple rows but the same account number.
> > Is there a way to sort and/or filter that will identify all of the multiple
> > account numbers without having to go through the entire list by hand?
> > I tried advance filter for unique records, hoping that if I pulled out all
> > of the people who didn't have multiple addresses it would at least help
> > narrow the records down, but nothing happened at all when I used that filter.
> >
> > Thanks for any help you can provide!
 | 
Pages: 1
Prev: how do I get rid of ###### that appear instead of text in excel?
Next: Excel On-line