Ну можно ещё так, первый вариант макросом
Код |
---|
Sub Макрос3()
Dim SortedList, Sp1, Sp2
Dim sl As String, n As Long, m As Long, z As Long
Sp1 = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Set SortedList = CreateObject("System.Collections.SortedList")
For n = LBound(Sp1) To UBound(Sp1)
If SortedList.ContainsKey(Sp1(n, 1)) = False Then
SortedList.Add Sp1(n, 1), CStr(Sp1(n, 1))
End If
Next n
z = IIf(SortedList.Count Mod 9 = 0, SortedList.Count / 9, Fix(SortedList.Count / 9) + 1)
ReDim Sp2(1 To z, 1 To 1)
m = 1
For n = 0 To SortedList.Count - 1
sl = sl & " " & SortedList.GetByIndex(n)
If n = SortedList.Count - 1 And (n + 1) Mod 9 <> 0 Then Sp2(m, 1) = Mid(sl, 2)
If (n + 1) Mod 9 = 0 Then Sp2(m, 1) = Mid(sl, 2): sl = "": m = m + 1
Next n
Range("C1:C" & z) = Sp2
End Sub
|
второй пользовательской функцией (вводится как формула массива)
Код |
---|
Function СОРТ_ПО_9(source As Range)
Dim SortedList, Sp1, Sp2
Dim sl As String, n As Long, m As Long
Sp1 = source.Value
Set SortedList = CreateObject("System.Collections.SortedList")
For n = LBound(Sp1) To UBound(Sp1)
If SortedList.ContainsKey(Sp1(n, 1)) = False Then
SortedList.Add Sp1(n, 1), CStr(Sp1(n, 1))
End If
Next n
ReDim Sp2(1 To IIf(SortedList.Count Mod 9 = 0, SortedList.Count / 9, Fix(SortedList.Count / 9) + 1), 1 To 1)
m = 1
For n = 0 To SortedList.Count - 1
sl = sl & " " & SortedList.GetByIndex(n)
If n = SortedList.Count - 1 And (n + 1) Mod 9 <> 0 Then Sp2(m, 1) = Mid(sl, 2)
If (n + 1) Mod 9 = 0 Then Sp2(m, 1) = Mid(sl, 2): sl = "": m = m + 1
Next n
СОРТ_ПО_9 = Sp2
End Function
|