Data Validation Query

[ramzraja]

Hi All,

Could you kindly help me how to validate below data set in excel ?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Group_Name[/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]Group 1[/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD]Group 2[/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD]Group 3[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]Group 2[/TD]
[/TR]
</tbody>[/TABLE]

I would like to write a macro to validate that every group should only have one distinct ID
In the above example Group 2 are assigned with two ID's 113 & 115 that should be highlighted using the macro.

Thanks,
Rameez Shaik

 

[tyija1995]

Hey try this macro:

Sub ADv()
'
' ADv Macro
'


'
Dim Cell As Range
Dim rng As Range
Set rng = Range("B2:B5")
Dim rng2 As Range
Set rng2 = Range("A2:A5")


     rng.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=OR(COUNTIF($B$2:$B$5,B2)=1,SUMPRODUCT(--(A2=$A$2:$A$5)*(B2=$B$2:$B$5))>1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
        For Each Cell In rng
            If WorksheetFunction.CountIf(rng, Cell) > 1 And WorksheetFunction.CountIf(rng2, Cell.Offset(0, -1)) = 1 Then
                Cell.Interior.ColorIndex = 36
            End If
        Next Cell
End Sub

I assume the IDs are in cells A2:A5 and the Group names in cells B2:B5 - change as appropriate to your situation (marked in red)

 

[ramzraja]

Hey try this macro:

Sub ADv()
'
' ADv Macro
'


'
Dim Cell As Range
Dim rng As Range
Set rng = Range("B2:B5")
Dim rng2 As Range
Set rng2 = Range("A2:A5")


     rng.Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=OR(COUNTIF($B$2:$B$5,B2)=1,SUMPRODUCT(--(A2=$A$2:$A$5)*(B2=$B$2:$B$5))>1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
        For Each Cell In rng
            If WorksheetFunction.CountIf(rng, Cell) > 1 And WorksheetFunction.CountIf(rng2, Cell.Offset(0, -1)) = 1 Then
                Cell.Interior.ColorIndex = 36
            End If
        Next Cell
End Sub

I assume the IDs are in cells A2:A5 and the Group names in cells B2:B5 - change as appropriate to your situation (marked in red)

Thank you so much for the code.
I have other validation code as below which i am trying to integrate with your suggestion.
I am asking the user to fill the last line of the data in excel so that it can be validated till that point. I am some how getting errors when i put your logic with mine.


Code to check if the column is NULL

Sub DataValidate()
Dim myRange As Integer
myRange = Application.InputBox(prompt:="Enter the last row number to be validate:")
ActiveCell.Activate
Range("B2").Select
exceptionCount = 0
Do Until ActiveCell.Row = myRange
' Check if the ACC_MGR_ID is Not Null
If IsEmpty(ActiveCell.Value) Then
totalExceptions = 1
exceptionCount = 1
ActiveCell.Interior.ColorIndex = 6 'Highlight with Yellow Color
Else
ActiveCell.Interior.ColorIndex = 15 'Retain Grey Color
End If
ActiveCell.Offset(1, 0).Select
Loop
If exceptionCount = 1 Then
MsgBox "ACC_MGR_ID  Cannot be Empty"
End If
End Sub

I would really appreciate if you could guide me to integrate in this regards.

Thank you.