Prevent duplication in three columns with a drop-down list
- Thread starter Hany ali
- Start date
Thank you very much for your response
This is considered a schedule for organizing and following up a football league, so it is not required to repeat the name of the first team and the second team for the same week. This is considered a mistake, so one must play one match in the same week and no more than that.
This is considered a schedule for organizing and following up a football league, so it is not required to repeat the name of the first team and the second team for the same week. This is considered a mistake, so one must play one match in the same week and no more than that.
Hany ali
One more time ... Did You answer to my question?
Did You read my writing at all?
That sample works with those red cells as well as below...
it is not required to repeat the name of the first team and the second team for the same week.
Your above picture is small and different than Your original.
Your logic is something to prevent ... that's very negative
... if something should allow ... that would be positive.
Is there something 'of course'? ... I know that Excel cannot understand 'of course's ... everything have to tell.
One more time ... Did You answer to my question?
Did You read my writing at all?
That sample works with those red cells as well as below...
it is not required to repeat the name of the first team and the second team for the same week.
Your above picture is small and different than Your original.
Your logic is something to prevent ... that's very negative
... if something should allow ... that would be positive.
Is there something 'of course'? ... I know that Excel cannot understand 'of course's ... everything have to tell.
vletm
thanks alot my Dear...
But I do not want to delete the name of the previously selected team from the list
Because it will be Select again with different weeks
p45cal
thanks my Dear ...But I don't just want to color by Conditional Formatting, but rather I want a code that prevents me from entering the repeater with the three columns
if i want by Conditional Formatting ,, I used Before this Formula and it's work as Well
thanks alot my Dear...
But I do not want to delete the name of the previously selected team from the list
Because it will be Select again with different weeks
p45cal
thanks my Dear ...But I don't just want to color by Conditional Formatting, but rather I want a code that prevents me from entering the repeater with the three columns
if i want by Conditional Formatting ,, I used Before this Formula and it's work as Well
Code:
=COUNTIFS($A$2:$A$500,$A2,$C$2:$C$500,$C2,$E$2:$E$500,$E2)>1
Last edited:
Hany ali
What has deleted?
... sorry, above is a question again.
My way was one possible way to prevent those duplicates - almost make sure that won't happen.
I could have few more solutions, but Your 'want' seems to change every time.
... as well as You still 'want' that there could be same teams in A & C -columns - okay?
What has deleted?
... sorry, above is a question again.
My way was one possible way to prevent those duplicates - almost make sure that won't happen.
I could have few more solutions, but Your 'want' seems to change every time.
... as well as You still 'want' that there could be same teams in A & C -columns - okay?
@vletm
This is an excellent solution, exactly This is what is required ..thank you very much
@Belleke
exactly This is what is required, I thank you very much
vletm
Now, I thank you very much. The last solution, of course, is the best solution provided by you
What has deleted?
What I mean by this is for your solution in Post No. 6
Which is when a specific team is chosen as a first team, for example, with a specific week number, I am surprised, the name of this team has been deleted and removed from the list. Although this same team will be selected after that, but with a different week number, and this is what I did not want ... and I thank you again for submitting Aid is always for everyone who needs it
This is an excellent solution, exactly This is what is required ..thank you very much
@Belleke
exactly This is what is required, I thank you very much
vletm
Now, I thank you very much. The last solution, of course, is the best solution provided by you
What has deleted?
What I mean by this is for your solution in Post No. 6
Which is when a specific team is chosen as a first team, for example, with a specific week number, I am surprised, the name of this team has been deleted and removed from the list. Although this same team will be selected after that, but with a different week number, and this is what I did not want ... and I thank you again for submitting Aid is always for everyone who needs it
p45cal
Well-Known Member
Is this allowed?:
I was able to do this in @vletm 's file, but that file wouldn't open properly with Excel complaining it had to repair it and remove some data validation, but I don't think it mattered because @vletm 's code adds and removes its own data validation, but maybe something else was damaged - perhaps to do with @vletm using a Mac?
Anyway, the attached doesn't allow what's in the picture above, but even if you should try to override the data validation it will still highlight errors.
This has been interesting challenge and one where I experimented with the newish FILTER and UNIQUE functions - hard work using the vba version of the sheet FILTER function! It's probably easier to use traditional looping as @vletm has done, although his code may need a tweak if the above picture is a situation which should be prevented.
My code could be shorter but I haven't bothered.
24 teams and 17 weeks could occupy 204 rows with each team playing only 1 match per week but I have included data validation and conditional formatting up to row 500 on Sheet1.
I think I've prevented all undesirable combinations via the drop downs, however if someone can point out any mistake I may have made I'd be glad to revise.
I was able to do this in @vletm 's file, but that file wouldn't open properly with Excel complaining it had to repair it and remove some data validation, but I don't think it mattered because @vletm 's code adds and removes its own data validation, but maybe something else was damaged - perhaps to do with @vletm using a Mac?
Anyway, the attached doesn't allow what's in the picture above, but even if you should try to override the data validation it will still highlight errors.
This has been interesting challenge and one where I experimented with the newish FILTER and UNIQUE functions - hard work using the vba version of the sheet FILTER function! It's probably easier to use traditional looping as @vletm has done, although his code may need a tweak if the above picture is a situation which should be prevented.
My code could be shorter but I haven't bothered.
24 teams and 17 weeks could occupy 204 rows with each team playing only 1 match per week but I have included data validation and conditional formatting up to row 500 on Sheet1.
I think I've prevented all undesirable combinations via the drop downs, however if someone can point out any mistake I may have made I'd be glad to revise.
Last edited:
p45cal
I noticed that Error myself too.
I solved it by saving file before I closed it - then that error didn't occure.
... isn't it almost normal to save file?
There could be many (im)possible combinations which should notice, if there were clear rules.
... and I really didn't test many of those ... eg both teams could be same.
... my the 1st version prevents those duplications ... for a good reason.
Thread is Prevent duplication in three columns with a drop-down list
and someway Your notice won't be duplicated as well as someway those could be.
I noticed that Error myself too.
I solved it by saving file before I closed it - then that error didn't occure.
... isn't it almost normal to save file?
There could be many (im)possible combinations which should notice, if there were clear rules.
... and I really didn't test many of those ... eg both teams could be same.
... my the 1st version prevents those duplications ... for a good reason.
Thread is Prevent duplication in three columns with a drop-down list
and someway Your notice won't be duplicated as well as someway those could be.