Alphabetical Order (check for duplicates)

[F. Lawrence Kulchar]

I have a column of names, in alphabetical order.

How can I see if I, perhaps, hve entered a name MORE THAN ONCE; in other
words, the listing would appear at least twice...consecutive rows would have
identical data -- which I wish to avoid??

Thank you,

FLKulchar

 

[Mike H]

Hi,

An excellent source for code, formula and formatting that checks for
duplicates is here

http://www.cpearson.com/excel/Duplicates.aspx

Mike

 

[MartinW]

Hi Lawrence,

With your data in column A put this in B1 and double click
on the fill handle. (or drag down to the end of your data)
=COUNTIF(A:A,A1)

It will return 1 for a single entry and 2 or greater for duplicates.

HTH
Martin

 

[FLKulchar]

Thank you -- works perfectly!!

FLKulchar

Hi Lawrence,

With your data in column A put this in B1 and double click
on the fill handle. (or drag down to the end of your data)
=COUNTIF(A:A,A1)

It will return 1 for a single entry and 2 or greater for duplicates.

HTH
Martin

 

[MartinW]

You're welcome Lawrence, just a little extra, although that formula
works fine, it does a little bit of extra un-necessessary work.
It's more efficient to set the range to be checked like this
=COUNTIF(A$1:A$1000,A1)
this saves on recalculation time but loses the dynamic capability.

HTH
Martin

 

[FLKulchar]

Thanks again...your original formula...

=COUNTIF(A:A,A1)

I DID NOT KNOW YOU COULD SELECT A COLUMN WITHOUT A ROW (OR
ROWS)...therefore, I totally understand your 2nd formula a little better.

Question: In...=COUNTIF(A:A,A1), does EXCEL actually check the entire
column A, i.e. all 65,536 rows??

FLKulchar

 

[MartinW]

Question: In...=COUNTIF(A:A,A1), does EXCEL actually check the entire
column A, i.e. all 65,536 rows??

Yes it does. I'm not sure on the exact time difference, but there
is one and it is something to keep in mind when creating large worksheets.

Having said that, don't be afraid to use references like A:A or 1:1 etc.
they come in very handy in many situations. Just keep it in mind
when you have a spreadsheet that has started to slow during
recalculation.

Charles Williams has a lot of good info in this article.
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

And also at his website.
http://www.decisionmodels.com/

HTH
Martin

 

[FLKulchar]

Thank you so much!

FLKulchar

Yes it does. I'm not sure on the exact time difference, but there
is one and it is something to keep in mind when creating large worksheets.

Having said that, don't be afraid to use references like A:A or 1:1 etc.
they come in very handy in many situations. Just keep it in mind
when you have a spreadsheet that has started to slow during
recalculation.

Charles Williams has a lot of good info in this article.
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

And also at his website.
http://www.decisionmodels.com/

HTH
Martin