Restricting cell data to unique entries?

[Jones]

Again, I've looked and looked under MS Excel help and on the web. I
think I'm again not asking the right question as nothing comes up.

How can we restrict data in a column so that the cells have a unique
value (i.e., to avoid repetition). It's tedious right now as I often
forget to do a search to make sure I haven't already entered the
record initially because it's a log and the folders keep coming back
to be updated. If I had a data restriction present in the cell that
allow for blank entries but not repeats in numbers, that would solve
this problem.

I've looked under data validation and done a search on data
restrictions and unique, etc., but nothing.

Thanks for any help!

 

[hcj]

Hi,
I don't think there is a way to restrict data entry to
uniques. However, you can apply autofilter to the data,
and that would at least SHOW you which values are already
in the set. If you wanted to update a record already in
the set, just pick it from the autofilter list. Don't
forget to restore the whole list by choosing (All) from
the top of the autofilter list.

Not a fix, but I hope it helps.

 

[Frank Kabel]

Hi
see: http://www.cpearson.com/excel/NoDupEntry.htm

 

[Jones]

Hi
see: http://www.cpearson.com/excel/NoDupEntry.htm

Awesome, this worked perfectly! I tried it on a test file and got
excellent results each and every time. I'll apply the formula to that
column in the file tomorrow at work. I understand that this can be
circumvented by pasting into the cell, etc., but this will be so much
better than what I have now with no validation. I'd have preferred to
write a database for these logs but was not authorized to do so and
was told to use Excel. I can still deliver quite a bit, though, thanks
to all these helpful tricks!

Thanks!

 

[Jones]

Hi,
I don't think there is a way to restrict data entry to
uniques. However, you can apply autofilter to the data,
and that would at least SHOW you which values are already
in the set. If you wanted to update a record already in
the set, just pick it from the autofilter list. Don't
forget to restore the whole list by choosing (All) from
the top of the autofilter list.

Thanks I already have this sheet autofiltered. I've not yet entered
100 of the several hundred records there are and already it's become
cumbersome!

As the next msg in this thread showed, there is a formula that does
handle duplicate entries.

Thanks so much, though, for this. Autofilter does do a great job in
many, many ways.

Cheers!

 

[Jones]

Awesome, this worked perfectly! I tried it on a test file and got
excellent results each and every time. I'll apply the formula to that
column in the file tomorrow at work. I understand that this can be
circumvented by pasting into the cell, etc., but this will be so much
better than what I have now with no validation. I'd have preferred to
write a database for these logs but was not authorized to do so and
was told to use Excel. I can still deliver quite a bit, though, thanks
to all these helpful tricks!

Thanks!

Well, OF COURSE, test files work great, don't they?? Reality is a
little different, however. <g>

Here are 3 problems occurring here in the file at work:

1) On copying the formula into the cell, it overwrites the existing
record number that we're trying to avoid dups of to begin with. So I
foresee a half an hour of extremely careful re-writing to put the ID
number back in. Not looking forward to this, but needs to be done.

2) Once this formula is placed in an empty row, the cell shows the
word TRUE in it. This is not good. Any way to have it blank?

3) And then of course, since there is now data in the cell, the
formula from the row number column to the left of the record ID kicks
in and a number appears (complicated by the fact that until a NUMBER
appears there, it mislabels these cells) <g>. Phew! It's set to show
something only if there is data present in the second cell which there
now is. But I'm hoping that once the TRUE is gotten rid of, all that
won't happen anymore, so once 2) is fixed, 3) might be a moot point.

Other than that, it works absolutely perfectly! <lol>

Thanks.

 

[Bridget]

Well, OF COURSE, test files work great, don't they?? Reality is a
little different, however. <g>

Here are 3 problems occurring here in the file at work:

1) On copying the formula into the cell, it overwrites the existing
record number that we're trying to avoid dups of to begin with. So I
foresee a half an hour of extremely careful re-writing to put the ID
number back in. Not looking forward to this, but needs to be done.

2) Once this formula is placed in an empty row, the cell shows the
word TRUE in it. This is not good. Any way to have it blank?

3) And then of course, since there is now data in the cell, the
formula from the row number column to the left of the record ID kicks
in and a number appears (complicated by the fact that until a NUMBER
appears there, it mislabels these cells) <g>. Phew! It's set to show
something only if there is data present in the second cell which there
now is. But I'm hoping that once the TRUE is gotten rid of, all that
won't happen anymore, so once 2) is fixed, 3) might be a moot point.

Other than that, it works absolutely perfectly! <lol>

Thanks.

Anyone?

Thank you so much!

 

[StargateFan]

Well, OF COURSE, test files work great, don't they?? Reality is a
little different, however. <g>

Here are 3 problems occurring here in the file at work:

1) On copying the formula into the cell, it overwrites the existing
record number that we're trying to avoid dups of to begin with. Means
a tedious and careful session of re-writing to put the ID
number back in after pasting. Any way around this behaviour?

2) Once this formula is placed in an empty row, the cell shows the
word TRUE in it. This is not good. Any way to keep it blank?

3) And then of course, since there is now data in the cell, the
formula from the row number column to the left of the record ID kicks
in and a number appears (complicated by the fact that until a NUMBER
appears there, it mislabels these cells) <g>. Phew! It's set to show
something only if there is data present in the second cell which there
now is. But I'm hoping that once the TRUE is gotten rid of, all that
won't happen anymore, so once 2) is fixed, 3) might be a moot point.

Other than that, it works absolutely perfectly! <lol>

Thanks.

 

[RagDyeR]

From what you're describing here, you are *not* doing it correctly.

The Countif() formula *DOES NOT* go in the cell !!!

It should be entered in the Data Validation Custom Formula bar!

AND ... if done correctly, it will *not* disturb any data that presently
exists in the cells that you are validating.

The only thing that you have to look out for, is that if you *already have
duplicates* entered in the column, the installation of the validation
formula will not "catch" them.

NOW, this is what to do:

Lets say that the cells concerned are in column A, from A1:A100.
It doesn't matter if they already have data, or if they're empty.

Select A1:A100.
While the cells are selected,
<Data> <Validation>
Expand the "Allow" box, and click on "Custom".
NOW ... in the formula box, enter this formula:

=COUNTIF($A$1:$A$100,A1)=1

Then click on the "Error Alert" tab,
And make sure that there's a check in the "Show Error Alert" box,
Then <OK>.

And you're done!

Now, your data is still undisturbed, and the empty cells are still empty (no
True)

You can now do your testing, and check for pre-existing duplicates.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

(e-mail address removed) (Jones) wrote in message

Well, OF COURSE, test files work great, don't they?? Reality is a
little different, however. <g>

Here are 3 problems occurring here in the file at work:

1) On copying the formula into the cell, it overwrites the existing
record number that we're trying to avoid dups of to begin with. Means
a tedious and careful session of re-writing to put the ID
number back in after pasting. Any way around this behaviour?

2) Once this formula is placed in an empty row, the cell shows the
word TRUE in it. This is not good. Any way to keep it blank?

3) And then of course, since there is now data in the cell, the
formula from the row number column to the left of the record ID kicks
in and a number appears (complicated by the fact that until a NUMBER
appears there, it mislabels these cells) <g>. Phew! It's set to show
something only if there is data present in the second cell which there
now is. But I'm hoping that once the TRUE is gotten rid of, all that
won't happen anymore, so once 2) is fixed, 3) might be a moot point.

Other than that, it works absolutely perfectly! <lol>

Thanks.

 

[StargateFan]

From what you're describing here, you are *not* doing it correctly.

The Countif() formula *DOES NOT* go in the cell !!!

It should be entered in the Data Validation Custom Formula bar!

<lol> Oh my goodness, newbie strikes again!

I had no idea, as you can guess. I only just learned about this whole
Data Validation in last few days.

AND ... if done correctly, it will *not* disturb any data that presently
exists in the cells that you are validating.

Great! Good to know.

The only thing that you have to look out for, is that if you *already have
duplicates* entered in the column, the installation of the validation
formula will not "catch" them.

Hmmm, of course. Stands to reason.

NOW, this is what to do:

Lets say that the cells concerned are in column A, from A1:A100.
It doesn't matter if they already have data, or if they're empty.

Select A1:A100.
While the cells are selected,
<Data> <Validation>
Expand the "Allow" box, and click on "Custom".
NOW ... in the formula box, enter this formula:

=COUNTIF($A$1:$A$100,A1)=1

Then click on the "Error Alert" tab,
And make sure that there's a check in the "Show Error Alert" box,
Then <OK>.

And you're done!
Awesome!

Now, your data is still undisturbed, and the empty cells are still empty (no
True)

You can now do your testing, and check for pre-existing duplicates.

Thanks so much! I'll give this a try.

Excel rocks!

 

[StargateFanFromWork]

From what you're describing here, you are *not* doing it correctly.

The Countif() formula *DOES NOT* go in the cell !!!

It should be entered in the Data Validation Custom Formula bar!

AND ... if done correctly, it will *not* disturb any data that presently
exists in the cells that you are validating.

The only thing that you have to look out for, is that if you *already have
duplicates* entered in the column, the installation of the validation
formula will not "catch" them.

NOW, this is what to do:

Lets say that the cells concerned are in column A, from A1:A100.
It doesn't matter if they already have data, or if they're empty.

Select A1:A100.
While the cells are selected,
<Data> <Validation>
Expand the "Allow" box, and click on "Custom".
NOW ... in the formula box, enter this formula:

=COUNTIF($A$1:$A$100,A1)=1

Then click on the "Error Alert" tab,
And make sure that there's a check in the "Show Error Alert" box,
Then <OK>.

And you're done!

Now, your data is still undisturbed, and the empty cells are still empty (no
True)

You can now do your testing, and check for pre-existing duplicates.

<DANCING A JIG>

Awesome, this is fabulous! It works perfectly. I'm glad I learned about
data validation in last few days so could implement what you wrote above
very easily. What is also really, really neat is how Excel allows us to
customize the error message. Totally, totally unbelievable how great this
sheet is working out. They were adamant it was to be Excel, it needs
Access, but we've gotten around a lot of the limitations of a spreadsheet
for tracking data with all these tricks.

Thanks so very, very much for helping me with this; I have learned sooo much
in the last 2 weeks, it's mind-boggling!

Have a great day!

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

From what you're describing here, you are *not* doing it correctly.

The Countif() formula *DOES NOT* go in the cell !!!

It should be entered in the Data Validation Custom Formula bar!

AND ... if done correctly, it will *not* disturb any data that presently
exists in the cells that you are validating.

The only thing that you have to look out for, is that if you *already have
duplicates* entered in the column, the installation of the validation
formula will not "catch" them.

NOW, this is what to do:

Lets say that the cells concerned are in column A, from A1:A100.
It doesn't matter if they already have data, or if they're empty.

Select A1:A100.
While the cells are selected,
<Data> <Validation>
Expand the "Allow" box, and click on "Custom".
NOW ... in the formula box, enter this formula:

=COUNTIF($A$1:$A$100,A1)=1

Then click on the "Error Alert" tab,
And make sure that there's a check in the "Show Error Alert" box,
Then <OK>.

And you're done!

Now, your data is still undisturbed, and the empty cells are still empty (no
True)

You can now do your testing, and check for pre-existing duplicates.

<DANCING A JIG>

Awesome, this is fabulous! It works perfectly. I'm glad I learned about
data validation in last few days so could implement what you wrote above
very easily. What is also really, really neat is how Excel allows us to
customize the error message. Totally, totally unbelievable how great this
sheet is working out. They were adamant it was to be Excel, it needs
Access, but we've gotten around a lot of the limitations of a spreadsheet
for tracking data with all these tricks.

Thanks so very, very much for helping me with this; I have learned sooo much
in the last 2 weeks, it's mind-boggling!

Have a great day!