Alphabetical Order (check for duplicates)

  • Thread starter Thread starter F. Lawrence Kulchar
  • Start date Start date
F

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
 
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
 
Thank you -- works perfectly!!

FLKulchar
MartinW said:
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
Click to expand...
 
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
 
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
 
Question: In...=COUNTIF(A:A,A1), does EXCEL actually check the entire
column A, i.e. all 65,536 rows??
Click to expand...

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
 
Thank you so much!

FLKulchar
MartinW said:
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
Click to expand...
 
Back
Top