I want to use VBA to extract a unique, ordered list, subject to conditions in another column. So, I have two columns A, B.
A B ======== a FALSE b FALSE c TRUE a TRUE b FALSE c TRUE
Should result in a list
C == a c
I'm very, very new to VBA, so any help would be appreciated.
Oh, and the second list will be updated with every change to the first, so needs to be wiped to ensure there are no leftovers, if, for example, the second "a" is set to FALSE.
What do you think of this? Add the MS Scripting library first.
Sub Test() Dim oRange As Range Dim dict As Dictionary Dim vArray As Variant Dim vItem As Variant Dim sKey As String Dim sValue As String Dim iCompare_TRUE As Integer Dim lCnt As Long Dim lCnt_Rows As Long Set dict = New Dictionary Set oRange = ThisWorkbook.Sheets(1).Range("A1:B6") For lCnt = 1 To oRange.Rows.Count sKey = oRange(lCnt, 1) sValue = oRange(lCnt, 2) iCompare_TRUE = StrComp(sValue, "True") If Not dict.exists(sKey) And iCompare_TRUE = 0 Then With dict .Add sKey, sValue End With End If Next lCnt ReDim vArray(1 To dict.Count) vArray = dict.Keys lCnt_Rows = UBound(vArray) + 1 Set oRange = ThisWorkbook.Sheets(1).Range(Cells(1, 3), Cells(lCnt_Rows, 3)) oRange.Value = Application.Transpose(vArray) End Sub