Create Unique List based on matching value in adjacent column

Hi everyone,

I have two columns, A and B. Column A has a unique list of values, and column B has a set number of value for each value in Column A.

What I'm trying to do is get macro that will give me a unique list of Column A values who's adjacent Column B value matches a predetermine value. I have attached a workbook to this post. This unique list of values will be pasted to column C

So, if the user wants to create a unique list based on the value "Yes" in column B, then the corresponding unique list will be pasted into column C. For this macro, a pivot table or manually created a unique list will not work. It must be automated via VBA.

Any ideas?

Thanks!

Try the attached.

Hi AB33,

Thank you very much for your macro! I'm embarrassed that I need make an error with my description and sample. Columns A and B in the sample template I attached should be in sheet 1, whereas the results column (currently column C) should be in sheet 2. Can you assist with these updates?

Thanks very much

hi bakerman,

Thanks very much for the updated code. I've made the following adjustments to match my actual workbook:

Sub tst()
    sn = Sheets("Summary").Cells(1).CurrentRegion.Value
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(sn)
            If sn(i, 16) = "Y" Then x0 = .Item(sn(i, 14))
        Next
        With Sheets("Data")
            .Columns(1).ClearContents
            .Cells(1).Resize(.Count) = Application.Transpose(.keys)
        End With
    End With
End Sub

16 is the column I'm matching the data to, and column 14 is were the unique list data is coming from. I'm getting a run-time error 438: object doesn't support this property or method on the following line:

.Cells(1).Resize(.Count) = Application.Transpose(.keys)

Any help? Also, i'm not certain where in your code it specifies where the unique list should be pasted in sheet Data.

Thanks for your help!

Adjusted to sheet 2

Thank you very much, AB33 and bakerman2

Maybe another option..?

Sub Test()
With [A1].CurrentRegion.Offset(, 2).Resize(, 1)
   .Value = [=IF(B1:B10="Y",A1:A10,"")]
   .SpecialCells(4).Delete
End With
End Sub

This is just using a static range.. to introduce a variable range.. use the evaluate function (without square brackets)..