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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]Etc..[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name 1[/TD]
[TD]Name 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name 2[/TD]
[TD][/TD]
[TD]Name 5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name 3[/TD]
[TD][/TD]
[TD]Name 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name 4[/TD]
[TD]Name 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name 5[/TD]
[TD][/TD]
[TD]Name 4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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