check if a range of cells contains the same word twice

[tigfur]

You've read the title, now, how do i do that?

i have cells in a single row, and i want to make sure that all those cells never contain the same word twice in that single row


it should prevent the user from assigning two task to a single person at the same time

[Kaper]

Use Data validation with custom validation rule.
See attached file
In yellow cells there is a rule:

Please Login or Register  to view this content.

checking if all values appeared only once

[Ochimus]

tigfur,

Attached file uses Data Validation as below to prevent you from entering same name twice:

=COUNTIF($B$2:$G$2,B2)<=1

TO test it, enter name in B2, then try same name anywhere between C2 and G2.

Message alerts you this name has been used already.

Enter different name in C2, and again in D2.

Message alerts you that name has also been used already.

Ochimus

[tigfur]

Thank you, it works just as i hoped it would!
however there is one problém, since it uses name validation, it is impossible to use with lists which i hoped to do
any way to prevent that?
i guess you cant use two validations on a single cell can you...
My idea would be to select the whle table and make linked copies that would be used for the formula and the others for the lists.. but thats kind of stupid...



Anyway as an addition, could this feature do the same with colors?

Dont allow the same color twice in one row, with the exception of a certain color, for example red

so i wouldnt be able to color any two cells with the same color except of red, red can be anywhere

[Kaper]

No, you can't have two validations in single cell. You would have to combine them. In that particular case, you probably shall create dynamically a list of allowable entries, excluding these already used.
Another option is to use VBA, and write cell Change event handler, which will work as "pseudo validation". or mix of the two - validation list will be dynamically updated by VBA, to contain only "unused names"

As for colors - you shall rather go into VBA direction. Regular functions are value-oriented (rather than formatting-oriented).

[tigfur]

well i tried to make a list like that using a few manuals online but those were always very complicated and requiered a lot of space and werent copyable at all if one needs those valuables say in every other row.

I found a manual where it lets you count colored cells, so that means functions can work with colors, i do have a few cells with no lists where i could apply the color validation to, just need to know the syntax when using colors..
then it hopefully could be a simple function like in the data validation counif(color values in cells;color values in cells;1) or so

[Kaper]

Some on-line manuals are better than others. To asses what could go wrong we shall know which manuals you refer to and check your attempts to implement them - so a good advice:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Because so far it seems that we (Ochimus and myself) are more involved than you :-)