Formula or VBA Function to List Unique values from a range of three columns

If Jeeped's solution works for you, definitely go with it - native worksheet functions are generally faster than VBA code.  But if you want/decide you need a VBA solution, here is one you could use.  It goes into a regular code module:

http://www.contextures.com/xlvba01.html#Regular

It could be set up to work every time you made one or more changes to the defined range but since it's clearing/refilling the column E data, that might be a little distracting.  But if you want to try that also, I'll post that code following this post.  Here's the code you have to call when you wanted to get the list of unique items from either the [View] or [Developer] tabs (or set up a button to click to call it).

Be sure to save the workbook as a macro enabled workbook, type .xlsm or .xlsb to preserve the code.

Sub ExtractUniqueEntries()

  Const ProductSheetName = "Sheet1" ' change as appropriate

  Const ProductRange = "B3:D50"

  Const ResultsCol = "E"

  Dim productWS As Worksheet

  Dim uniqueList() As String

  Dim productsList As Range

  Dim anyProduct

  Dim LC As Integer

  ReDim uniqueList(1 To 1)

  Set productWS = Worksheets(ProductSheetName)

  Set productsList = productWS.Range(ProductRange)

  Application.ScreenUpdating = False

  For Each anyProduct In productsList

    If Not IsEmpty(anyProduct) Then

      If Trim(anyProduct) <> "" Then

        For LC = LBound(uniqueList) To UBound(uniqueList)

          If Trim(anyProduct) = uniqueList(LC) Then

            Exit For ' found match, exit

          End If

        Next

        If LC > UBound(uniqueList) Then

          'new item, add it

          uniqueList(UBound(uniqueList)) = Trim(anyProduct)

          'make room for another

          ReDim Preserve uniqueList(1 To UBound(uniqueList) + 1)

        End If

      End If

    End If

  Next ' end anyProduct loop

  If UBound(uniqueList) > 1 Then

    'remove empty element

    ReDim Preserve uniqueList(1 To UBound(uniqueList) - 1)

  End If

  'clear out any previous entries in results column

  If productWS.Range(ResultsCol & Rows.Count).End(xlUp).Row > 1 Then

    productWS.Range(ResultsCol & 2 & ":" & _

     productWS.Range(ResultsCol & Rows.Count).Address).ClearContents

  End If

  'list the unique items found

  For LC = LBound(uniqueList) To UBound(uniqueList)

    productWS.Range(ResultsCol & Rows.Count).End(xlUp).Offset(1, 0) = _

     uniqueList(LC)

  Next

  'housekeeping cleanup

  Set productsList = Nothing

  Set productWS = Nothing

End Sub

You only have three columns and a limited number of rows to return so I would recommend this nested array formula.

The array formula in E3 is,

=IFERROR(INDEX($B$3:$B$99, MATCH(0, IF(LEN($B$3:$B$99), COUNTIF(E$2:E2, $B$3:$B$99),1),0)),IFERROR(INDEX($C$3:$C$99, MATCH(0, IF(LEN($C$3:$C$99), COUNTIF(E$2:E2, $C$3:$C$99),1),0)),IFERROR(INDEX($D$3:$D$99, MATCH(0, IF(LEN($D$3:$D$99), COUNTIF(E$2:E2, $D$3:$D$99),1),0)),"")))

This requires Ctrl+Shift+Enter rather than just Enter to finalize. It is essentially three 'gather unique' formulas that are nested within IFERROR() functions.

If you have much more than this sample data in your actual worksheet, a VBA routine using a scripting dictionary may be the best alternative.