How to prevent duplicates.....

[Keand31]

Hi all.

A small problem I just can't seem to wrap my head around...
I am trying to create a planner and I've tried countif, Datavalidation and conditional formating but when ever I think I am close no dice.

My challenge is this:
I have X number of people who all have to have lunch together each month but only one on one.

So if Person 1 has lunch with Person 2 they can't have lunch with person 3, 4, 5, and 6 that month.

My sheet would look something like this:

	A	B	C	D	E
1	Name	January	February	March	Etc..
2	Name 1	Name 2
3	Name 2		Name 5
4	Name 3		Name 1
5	Name 4	Name 3
6	Name 5		Name 4

<tbody>
</tbody>

What I need is to somehow check if the same combination exist in the same month?
IE. February where Name 2 & 4 both have a appointment with Name 5.

Like I said I just canøt wrap my head around it..
What gets me everytime is that I need to somehow check if name X is present in Column Month and the value can be either 0, 1 then true and if more than 1 then Error.

There might be a simple solution to this but I just can't see it

Sincerely
Kenneth Andersen

 

[footoo]

If VBA is OK :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, col&, rng As Range, area As Range, x%
If Target.count > 1 Then Exit Sub
Target.Interior.Color = xlNone
If Target = "" Then Exit Sub
col = Target.Column
lr = Cells(Rows.count, "A").End(xlUp).Row
Set rng = Cells(2, col).Resize(lr - 1).SpecialCells(xlCellTypeConstants)
Set rng = Union(rng.Offset(, -col + 1), rng)
For Each area In rng.Areas
    x = x + WorksheetFunction.CountIf(area, Target)
    If x > 1 Then
        MsgBox "Duplicate"
        Target.Interior.Color = vbRed
        Exit For
    End If
Next
If x < 2 Then
    x = 0
    For Each area In rng.Areas
        x = x + WorksheetFunction.CountIf(area, Target(1, -col + 2))
        If x > 1 Then
            MsgBox "Duplicate"
            Target.Interior.Color = vbRed
            Exit For
        End If
    Next
End If
End Sub

 

[Keand31]

Thank you so much Footoo - Works like a charm.
You have just saved me a lot of headache.

Sincerely
Kenneth