根据组号完成自动分组

kakanaole

根据组号完成自动分组.zip (6.18 KB, 下载次数: 12)

2017-5-29 14:49 上传

点击文件名下载附件
附件1

请高手帮忙用函数解决一下！

yeminqiang

index+small+if

gykg-excel

=IFERROR(INDEX($B:$B,SMALL(IF(($C$2:$C$7=COLUMN(A$1)),ROW($B$2:$B$7)),ROW(A1))),"")

凌空一羽

1. Sub lkyy()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. ar = Range("a1").CurrentRegion
   
4. ReDim br(1 To 100, 1 To 100)
   
5. For i = 2 To UBound(ar)
   
6.     If Not d.exists(ar(i, 3)) Then
   
7.         d(ar(i, 3)) = ar(i, 2)
   
8.     Else
   
9.         d(ar(i, 3)) = d(ar(i, 3)) & "," & ar(i, 2)
   
10.     End If
    
11. Next
    
12. For Each Key In d.keys
    
13.     n = n + 1
    
14.     br(1, n) = Mid("一二三", Key, 1) & "组"
    
15.     br(2, n) = Split(d(Key), ",")(0)
    
16.     br(3, n) = Split(d(Key), ",")(1)
    
17. Next
    
18. Set d = Nothing
    
19. Range("f1").Resize(3, 3) = br
    
20. End Sub

复制代码

铮铮try

=INDEX($B:$B,SMALL(($C$1:$C$7<>COLUMN(A1))/1%%+ROW($1:$7),ROW(A1)))&""
ctrl+shift+enter三键，下拉右拉

kakanaole

谢谢 楼上的高手帮忙解决！谢谢  辛苦了！

问山

IFERROR(INDEX($B:$B,SMALL(IF($C:$C=COLUMN(A1),ROW(A:A)),ROW(A1))),"")