在 Excel 中的另一列中创建给定条件的唯一列表

Question

显示我的问题图形的图像

Column B:  Column C:
1             A   
1             A          
1             B        
2             B        
2             C            
3             D     
3             D    

有没有办法根据 B 列中的值获取 C 列中的值？例如，如果 B 列中的值 = 1，则 C 列中的唯一值是什么？

Answer 1

使用 VBA/宏：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim wks As Worksheet
    Set wks = ActiveSheet
    filterrow = 2 'row of the filter cell
    filtercolumn = 4 'column of the filter cell
    criteriacolumn = 2 'number of column of the criteria list
    firstrow = 2 'first row on the criteria list
    resultcolumn = 5 'number of columns where to show the results
    thecriteria = Target.Value
    Dim critarray() As Variant
    therow = Target.Row
    thecolumn = Target.Column
    lastitem = 0
    wks.Columns(resultcolumn).ClearContents
    If (therow = filterrow) And (thecolumn = filtercolumn) Then
        lastrow = wks.Cells(Rows.Count, criteriacolumn).End(xlUp).Row
        ReDim critarray(lastrow)
        For i = firstrow To lastrow
            tempcriteria = wks.Cells(i, criteriacolumn)
            templist = wks.Cells(i, criteriacolumn + 1)
            If tempcriteria = Target.Value Then
                repeated = False
                For j = 1 To lastitem
                    a = critarray(j)
                    If a = templist Then
                        repeated = True
                        j = lastrow
                    End If
                Next j
                If repeated = False Then
                    lastitem = lastitem + 1
                    critarray(lastitem) = templist
                    wks.Cells(lastitem, resultcolumn) = templist
                End If
            End If
        Next i
    End If
    Application.EnableEvents = True
End Sub

使用 ALT+F11 双击工作表并粘贴右侧的代码来打开 VBA/宏。您可以修改变量以使宏适合您的工作表。

它使用单元格D2作为过滤值，并将结果输出到列埃。