Convert multiple numbers or text from a column into a row without duplicates

Question

See the image. I want the column to be converted into the row as in the image. Even if there are multiple text or numbers with commas in any single row within the column, then those needs to be considered as duplicates. How do I make the row that I have using a formula?

Are you looking for a VBA or formula solution?

VBA Pete
– VBA Pete

2017-01-05 18:03:42 +00:00
Commented Jan 5, 2017 at 18:03 — VBA Pete
– VBA Pete, Commented Jan 5, 2017 at 18:03

Gary's Student · Accepted Answer · 2017-01-05 18:02:00Z

1

Try this small user defined function:

Public Function MakeList(rng As Range) As String
    Dim c As Collection, r As Range, s As String
    Set c = New Collection
    For Each r In rng
        ary = Split(r.Value, ",")
        For Each a In ary
            On Error Resume Next
                c.Add a, CStr(a)
            If Err.Number = 0 Then
                MakeList = MakeList & "," & a
            Else
                Err.Number = 0
            End If
            On Error GoTo 0
        Next a
    Next r
    MakeList = Mid(MakeList, 2)
End Function

For example:

answered Jan 5, 2017 at 18:02

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DD1 Over a year ago

Where do I put the VBA code? I have no idea how to do this. I was actually looking for a built-in formula within excel

VBA Pete Over a year ago

PressAlt+11, then insert a module and paste the code into the module.

Collectives™ on Stack Overflow

Convert multiple numbers or text from a column into a row without duplicates

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related