Excel Search list of values, and return only unique values in horizontal format

Question

I need to generate a non-duplicated list of item numbers based on values in a range.

Two possible options:

Formula: Trying to search a range and return a corresponding value from another column. I am searching with a range of values, and need a list of only the unique items to generate.

Pivot Table:

Filter a pivot table based off a range of cells I can paste into. I have tried multiple options and haven't been able to get it to work.

On another sheet (BOM Sorting Sheet) search for "To Be Disc'd" values in Column A and return all values found from Column H (unique only).

Goal: Be able to copy and paste values from email to "To Be Dis'd" and a unique item list will generate automatically.

All help appreciated :)

I also tried...

Various formulas that I couldn't get to work, I am positive it is a user error, just not sure what I'm missing.

=Transpose(vlookup

=UNIQUE(FILTER(data,(range1="b")*(range2>5)))

{=INDEX(range1,MATCH(1,(A1=range2)(B1=range3)(C1=range4),0))}

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ' If Not Intersect(Target, Range("A15:A16")) Is Nothing Then
  'End If
 ' Dim vArray As Variant
 ' Dim b As Integer, c As Integer
 ' Dim pvFld As PivotField
 ' Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("*Item")
 ' vArray = Range("A15:A16")
 ' pvFld.ClearAllFilters

'  With pvFld
 '   For i = 1 To pvFld.PivotItems.Count
  '    j = 1
   '    Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1
    '     If pvFld.PivotItems(i).Name = vArray(a, 1) Then
     '      pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = True
      '     Exit Do
       ' Else
        '  pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = False
  '      End If
   '     j = j + 1
    '  Loop
 '   Next i
 ' End With
'End Sub

Answer 1

Goal: Entry point and Unique list of items based on a list of STK's copied from email.

If you would like to sort a Pivot Table automatically and get a list of data that is unique (not duplicated), this is the combination I used.

To sort a Pivot Table section based on a cell range NOT in your data source.

Step 1

Add 2 columns to your source data for your Pivot Table.

Column 1, reference the data entry cells (I added an entry point on the sheet that contained my Tracker for Disc'd items for easier sorting).

Column 2, add an ifs formula that references the data in column 1 and the data you want to auto filter give you a column with the data you need from the entry point only (A set number of cells that you can enter data into will simplify the Ifs function considerably, and you will not need to edit later).

Now you have a column that after refreshing the Pivot Table will automatically show the new list of data based on the Entry Range.

Set the filter on that column to automatically ignore errors, zeros, & blanks.

Step 2

Enter the formula (=UNIQUE($F:$F) in a column next to the pivot table. Change the column to reflect the unique data list wanted.

Step 3

Add a refresh button on the tracker sheet (or notes sheet). You can use a VBA Code to refresh the Pivot Table.

Sheet1.PivotTables("PivotTable1").Refreshtable

Step 4

Use a column to reference the Unique items pulled in the Pivot table.

Now you have everything needed on one sheet to enter, search and quickly take action on items.