Vba to search in column B and if unique value is just one then return it to cell A1 of sheet1

Flora Edwards
Flora Edwards used Ask the Experts™
on
I need help with VBA, the macro to search in column B of Sheet(2) and then return all unique values into cell A1 of Sheets(1)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try
Sub macro()
Set objDict = CreateObject("Scripting.Dictionary")
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
For Each c In Range(sh2.Range("B1"), sh2.Range("B" & Rows.Count).End(xlUp))
    If Not objDict.Exists(c.Value) Then objDict.Add c.Value, c.Value
Next

aDict = objDict.Items
sh1.Range("A1").Resize(UBound(aDict) + 1) = Application.Transpose(aDict)
End Sub

Open in new window

regards
Top Expert 2016
Commented:
or
Sub macro()
Set objDict = CreateObject("Scripting.Dictionary")
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
Set Rng = Range(sh2.Range("B1"), sh2.Range("B" & Rows.Count).End(xlUp))
For Each c In Rng
    If WorksheetFunction.CountIf(Rng, c.Value) = 1 Then
        objDict.Add c.Value, c.Value
    End If
Next

aDict = objDict.Items
sh1.Range("A1").Resize(UBound(aDict) + 1) = Application.Transpose(aDict)
End Sub

Open in new window

Flora EdwardsMedicine

Author

Commented:
Thanks Rgonzo.

sorry i mistyped.  the macro should return all unique not just one.

thanks.
Flora EdwardsMedicine

Author

Commented:
Thanks. it worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial