[color=blue]Sub[/color] clearexceldoubtsUniques()
[color=blue]Dim[/color] wks1 [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks1 = ThisWorkbook.Worksheets("clearexceldoubts")
[color=blue]Dim[/color] r [color=blue]As[/color] [color=blue]Long[/color], lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks1.Cells.Find(what:="*", After:=wks1.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
[color=blue]Dim[/color] dicOb1 [color=blue]As[/color] [color=blue]Object[/color], z [color=blue]As[/color] [color=blue]Variant[/color]
[color=blue]Set[/color] dicOb1 = CreateObject("Scripting.Dictionary")
[color=blue]Dim[/color] dicOb2 [color=blue]As[/color] [color=blue]Object[/color]
[color=blue]Set[/color] dicOb2 = CreateObject("Scripting.Dictionary")
[color=blue]Dim[/color] YKeys() [color=blue]As[/color] [color=blue]Variant[/color], NKeys() [color=blue]As[/color] [color=blue]Variant[/color], CntN [color=blue]As[/color] [color=blue]Long[/color], CntY [color=blue]As[/color] [color=blue]Long[/color]
[color=lightgreen]'Dim dicOb1 As Scripting.Dictionary, z As Variant[/color]
[color=lightgreen]'Set dicOb1 = New Scripting.Dictionary[/color]
[color=lightgreen]'Dim dicOb2 As Scripting.Dictionary[/color]
[color=lightgreen]'Set dicOb2 = New Scripting.Dictionary[/color]
[color=blue]For[/color] r = 3 [color=blue]To[/color] lr [color=blue]Step[/color] 1 [color=lightgreen]' go down each row starting at row 3[/color]
[color=blue]If[/color] wks1.Cells(r, 2).Value = "Y" [color=blue]Then[/color] [color=lightgreen]'Check for Y in column B[/color]
[color=blue]Let[/color] z = dicOb1.Item(wks1.Cells(r, 1).Value)
[color=blue]Else[/color] [color=lightgreen]'If no Y in column B then check for N in column B[/color]
[color=blue]If[/color] wks1.Cells(r, 2).Value = "N" [color=blue]Then[/color] [color=lightgreen]'Check for N in column B[/color]
[color=blue]Let[/color] z = dicOb2.Item(wks1.Cells(r, 1).Value)
[color=blue]Else[/color] [color=lightgreen]'Case Neither Y or N, so do nothing . Redundant code[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]End[/color] [color=blue]If[/color]
[color=blue]Next[/color] r [color=lightgreen]'go to next row[/color]
[color=lightgreen]' Output - Count of Y/N : Array with corresoonding Patients ID[/color]
[color=blue]Let[/color] CntY = dicOb1.Count: [color=blue]Let[/color] YKeys() = dicOb1.Keys
[color=blue]Let[/color] CntN = dicOb2.Count: [color=blue]Let[/color] NKeys() = dicOb2.Keys
[color=blue]Set[/color] dicOb1 = [color=blue]Nothing[/color]
[color=blue]Set[/color] dicOb2 = [color=blue]Nothing[/color]
[color=blue]End[/color] [color=blue]Sub[/color]