1

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.

Nico Nick
  • 25
  • 1
  • 5

2 Answers2

2

Here's a formula-only approach. Whether it's practical depends on your circumstances, but I have tested it with a sample of data similar to the one in the original question:

  1. Insert a blank row at the top of the spreadsheet to serve as a header row.
  2. Create a new formula column that will list the elements of column "A" only if column "B" is true. For example, place the following formula in cell D2, then copy it down: =IF(B2,A2,"").
  3. Now you can apply the technique described in the second page linked by t.thielemans above.

One potential disadvantage of this approach is that the blank cells returned by the formula when column B is "FALSE" don't disappear--you'll still have a blank result in your filtered view.

I'll copy the reference here for convenience: Getting unique values in Excel by using formulas only

Community
  • 1
  • 1
Tim
  • 1,755
  • 2
  • 22
  • 34
0

What do you think of this? Add the MS Scripting library first.

Option Explicit

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
html_programmer
  • 18,126
  • 18
  • 85
  • 158
  • Note that this is a basic example. In fact, if you want to do this on large chunks of data, loop through an array rather than a range! – html_programmer Oct 24 '12 at 13:45