如何实现在不加辅助列情况实现重复数据只显示一个

niko88819

Benfyhu 发表于 2022-11-14 09:10
谢谢！那如果是引用数据在SHEET2 A列。显示的引用值在SHEET1 A2，这个怎么修一下

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     If Intersect([f2], Target) Is Nothing Then Exit Sub
   
3.     If Target.Rows.Count > 1 Then Exit Sub
   
4.     Dim arr, brr, i&, j&, k&, s
   
5.     Dim d As Object
   
6.     Set d = CreateObject("scripting.dictionary")
   
7.     arr = sheet2.Range("a1:a" & sheet2.Range("a65536").End(xlUp).Row)
   
8.     If Not IsArray(arr) Then Exit Sub
   
9.     For i = 1 To UBound(arr)
   
10.         If arr(i, 1) <> "" Then d(arr(i, 1)) = ""
    
11.     Next
    
12.     s = Join(d.keys, ",")
    
13.     With Target.Validation
    
14.         .Delete
    
15.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=s
    
16.     End With
    
17.     Set d = Nothing
    
18. End Sub

复制代码

hcm19522

D2{==IFERROR(INDEX(T(OFFSET(A$1,MOD(MATCH(F$2,A:A,)+ROW($1:$20)-2,20),)),MATCH(,COUNTIF(D$1:D1,T(OFFSET(A$1,MOD(MATCH(F$2,A:A,)+ROW($1:$20)-2,20),))),)),"")

輔助H1:H20=OFFSET(A$1,MOD(MATCH(F$2,A:A,)+ROW(A1)-2,20),)

I2:I7{=IFERROR(INDEX(H:H,MATCH(,COUNTIF(I$1:I1,H$1:H$20),)),"")