Formula to extract unique values as a horizontal list

[willow1985]

Hello,

I have tried a few formulas (index match with count if) But I cannot seem to get the results I am looking for.

I have a list of areas that I would like unique values transposed horizontally (see below picture)

Any help would be appreciated ( I think I am missing something in my formula attempts)

Thank you!

 

[willow1985]

Did you put that in the Score Card sheet module?

Yes - see below

 

[Fluff]

Oops it should be

If Target.Address(0, 0) = "A1" Then

 

[willow1985]

Oops it should be

If Target.Address(0, 0) = "A1" Then

Ah, Didnt see that. Thank you!
Works perfect!

Just one more question: If I wanted to make this same code give results vertical instead of horizontal, what would I change?

 

[VBasic2008]

Is the Area column populated manually or by formula?

 

[Fluff]

You would use

      Me.Range("D3").Resize(Dic.Count).Value = Application.Transpose(Dic.Keys)

 

[willow1985]

You would use

      Me.Range("D3").Resize(Dic.Count).Value = Application.Transpose(Dic.Keys)

I apologize, one more question about the code, is it possible to take multiple data sources?
On mohadin's post he provided a code in which I also modified but I want to know if I can have multiple data sources.

Currently the list source is column H in sheet Log.
What if I had sheets: Log2, Log3 and so forth with the data in column H as well and I wanted to get a list of all of the unique values from 3 sheets instead of 1.

I was just told that the data source may be multiple...

I used the below VBA code as well as yours in the same sheet for something similar and it works great however a = Sheets("Log").Range("H2:H" & Cells(Rows.Count, 1).End(xlUp).Row) is one data source.

I also want
Sheets("Log2").Range("H2:H" & Cells(Rows.Count, 1).End(xlUp).Row)
Sheets("Log3").Range("H2:H" & Cells(Rows.Count, 1).End(xlUp).Row)

and only pull the unique values out of all 3 combined.

Is that also possible?

    Dim a As Variant, lr, i, x, s, k, itm
    a = Sheets("Log").Range("H2:H" & Cells(Rows.Count, 1).End(xlUp).Row)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), 0
                End If: End If
        Next
      Sheets("Log").Cells(1, 10).Resize(, .Count) = .keys
    End With

End Sub

Thank you for all the info on this code

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Cl As Range
   Dim Dic As Object
   Dim Ary As Variant
   Dim i As Long

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A1" Then
   
      Ary = Array("Log1", "Log2", "Log3")
      Set Dic = CreateObject("scripting.dictionary")
      Dic.comparemode = 1
      For i = 0 To UBound(Ary)
         With Sheets(Ary(i))
            For Each Cl In .Range("H2", .Range("H" & Rows.Count).End(xlUp))
               If Cl.Offset(, -5).Value = Target.Value Then Dic(Cl.Value) = Empty
            Next Cl
         End With
      Next i
      Me.Range("D3").Resize(, Dic.Count).Value = Dic.Keys
   End If
End Sub

 

[willow1985]

Thank you

 

[Fluff]

My pleasure.

 

[Peter_SSs]

@willow1985
Could I suggest that you take a look at the forum Guidelines, particularly 5.a.

5. Everyone here is a volunteer, so make it as easy as you can for people to help you.
a. State your question clearly, including your entire need at the start.

So in future, trying to avoid such a drawn-out process of incremental changes:

I have a list of areas that I would like unique values transposed horizontally (see below picture)

What if I wanted the horizontal list on another sheet,

I have to say I do like the idea of using VBA

What could I add to this code to clear D3:AA3 any time there is a change to A1

Just one more question: If I wanted to make this same code give results vertical

one more question about the code, is it possible to take multiple data sources?