Excel需求：基于指定范围生成自动更新的唯一物料编号列表

方案一：动态数组公式（Excel 365/2021+） #

在「Discontinued STK's」的A2单元格输入以下公式，自动生成唯一物料编号列表（无需下拉填充）：

=UNIQUE(FILTER('BOM Sorting Sheet'!H:H, 'BOM Sorting Sheet'!A:A="To Be Disc'd", "无匹配数据"))

• 逻辑：先用FILTER筛选出「BOM Sorting Sheet」中A列等于“To Be Disc'd”的H列值，再用UNIQUE去除重复项；无匹配时显示提示文本。
• 如需横向排列结果，嵌套TRANSPOSE：

=TRANSPOSE(UNIQUE(FILTER('BOM Sorting Sheet'!H:H, 'BOM Sorting Sheet'!A:A="To Be Disc'd", "无匹配数据")))

方案二：旧版Excel兼容方案（2019及更早） #

使用数组公式（输入后按Ctrl+Shift+Enter确认），在「Discontinued STK's」的A2单元格输入：

=INDEX('BOM Sorting Sheet'!H:H, MATCH(0, COUNTIF(A$1:A1, 'BOM Sorting Sheet'!H:H)+('BOM Sorting Sheet'!A:A<>"To Be Disc'd"), 0))

下拉填充直到出现#N/A，即可得到所有唯一值。

• 逻辑：通过COUNTIF排除已提取的重复值，结合MATCH定位符合条件的首个未提取值，最后用INDEX返回对应内容。

方案三：VBA自动触发（粘贴后自动更新） #

实现「Discontinued STK's」A列粘贴数据后自动生成唯一列表，步骤如下：

1. 按Alt+F11打开VBA编辑器；
2. 左侧工程窗口双击「Discontinued STK's」工作表；
3. 粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Columns("A")) Is Nothing Then
        Dim sourceWS As Worksheet, uniqueVals As Collection
        Dim cell As Range, val As Variant, lastRow As Long
        
        Set sourceWS = ThisWorkbook.Worksheets("BOM Sorting Sheet")
        Set uniqueVals = New Collection
        
        ' 收集符合条件的唯一值
        On Error Resume Next
        For Each cell In sourceWS.Range("A2:A" & sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).Row)
            If cell.Value = "To Be Disc'd" Then
                uniqueVals.Add sourceWS.Cells(cell.Row, "H").Value, Key:=CStr(sourceWS.Cells(cell.Row, "H").Value)
            End If
        Next cell
        On Error GoTo 0
        
        ' 清空旧结果（写入B列避免覆盖粘贴的A列数据）
        Me.Range("B2:B" & Me.Cells(Me.Rows.Count, "B").End(xlUp).Row).ClearContents
        
        ' 写入新结果
        lastRow = 2
        For Each val In uniqueVals
            Me.Cells(lastRow, "B").Value = val
            lastRow = lastRow + 1
        Next val
    End If
End Sub

• 说明：代码仅在A列被修改时触发，将结果写入B列（可根据需求修改目标列），自动跳过重复值。

方案四：数据透视表修正设置 #

调整之前的操作步骤，确保数据透视表正确提取唯一值：

1. 选中「BOM Sorting Sheet」的完整数据区域（含表头）；
2. 点击「插入」→「数据透视表」，将透视表放置在「Discontinued STK's」中；
3. 字段设置：将A列表头（如“状态”）拖至「筛选器」，H列表头（如“物料编号”）拖至「行」；
4. 筛选器选择“To Be Disc'd”，行区域将显示所有唯一物料编号；
5. 右键透视表→「数据透视表选项」，勾选「打开文件时刷新数据」实现自动更新。

内容的提问来源于stack exchange，提问作者Gerlina Warden