#### unique sort A to Z multi columns to single column start form beginning of destination column

Public Sub test()
unique_Sort_multiple_columnstoSinglecol "\$D\$5:\$K\$23", "A1"
End Sub

Public Sub unique_Sort_multiple_columnstoSinglecol(SourceRng As String, DestinationRng As String)
Dim Arr(1 To 15) As Variant

With Range(SourceRng)

MyRows = "ROWS(" & MyAddress & ")"
'or
'MyRows = "ROWS(\$1:\$" & .Rows.Count & ")"
MyColumns = "COLUMNS(" & MyAddress & ")"
MrRow = "ROW(\$1:\$" & .Rows.Count * .Columns.Count & ")"

'Sort text after number

Text = "(SUM(--ISNUMBER(" & MyAddress & "))*ISTEXT(" & MyAddress & "))" ' count numbers
Arr(2) = "(" & Arr(1) & "+" & Text & ")" 'add count of numer to text
Arr(3) = "SMALL(" & Arr(2) & "," & MrRow & ")" ' sort

' eliminate duplicate
Arr(4) = "(" & MrRow & "<" & MyRows & "*" & MyColumns & ")" ' to remove the largest
Arr(5) = "(" & Arr(3) & "*" & Arr(4) & ")"
Arr(6) = "SMALL(Arr(5),MrRow)" ' sort
Arr(7) = "(" & Arr(3) & "<>" & Arr(6) & ")"
Arr(8) = "(" & Arr(3) & "/" & Arr(7) & ")"
Arr(9) = "SMALL(IFERROR(Arr(8),""""),ROWS(\$1:1))" ' sort uniqu  small by small

Arr(10) = "(" & Arr(9) & "=" & Arr(2) & ")" ' get uniqu  small by small
'orgnize Rows And Columns
Arr(12) = "MAX(" & Arr(11) & "*" & Arr(10) & ")"
'Get rows
Arr(13) = "MOD(Arr(12)-1," & MyRows & ")+1" 'Get rows
'Get rows
Arr(14) = "CEILING(Arr(12),MyRows)/MyRows" 'Get columns

Arr(15) = "IFERROR(INDEX(" & MyAddress & "," & Arr(13) & "," & Arr(14) & "),"""")"
End With

With Range(DestinationRng)
.FormulaArray = "=" & Arr(15)
.Replace "Arr(12)", Arr(12)
.Replace "Arr(8)", Arr(8)
.Replace "Arr(6)", Arr(6)
.Replace "Arr(5)", Arr(5)
.Replace "Arr(3)", Arr(3)
.Replace "MrRow", MrRow
.Replace "MyRows", MyRows

.AutoFill Destination:=.Resize(Range(SourceRng).Rows.Count * Range(SourceRng).Columns.Count, 1)

End With

End Sub

=IFERROR(INDEX(\$D\$5:\$K\$23,MOD(MAX((ROW(\$D\$5:\$K\$23)-MIN(ROW(\$D\$5:\$K\$23))+1+((COLUMN(\$D\$5:\$K\$23)-MIN(COLUMN(\$D\$5:\$K\$23)))ROWS(\$D\$5:\$K\$23)))(SMALL(IFERROR((SMALL((COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))*ISTEXT(\$D\$5:\$K\$23))),ROW(\$1:\$152))/(SMALL((COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))*ISTEXT(\$D\$5:\$K\$23))),ROW(\$1:\$152))<>SMALL((SMALL((COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))ISTEXT(\$D\$5:\$K\$23))),ROW(\$1:\$152))(ROW(\$1:\$152)<ROWS(\$D\$5:\$K\$23)*COLUMNS(\$D\$5:\$K\$23))),ROW(\$1:\$152)))),””),ROWS(\$1:1))=(COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))*ISTEXT(\$D\$5:\$K\$23)))))-1,ROWS(\$D\$5:\$K\$23))+1,CEILING(MAX((ROW(\$D\$5:\$K\$23)-MIN(ROW(\$D\$5:\$K\$23))+1+((COLUMN(\$D\$5:\$K\$23)-MIN(COLUMN(\$D\$5:\$K\$23)))ROWS(\$D\$5:\$K\$23)))(SMALL(IFERROR((SMALL((COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))*ISTEXT(\$D\$5:\$K\$23))),ROW(\$1:\$152))/(SMALL((COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))*ISTEXT(\$D\$5:\$K\$23))),ROW(\$1:\$152))<>SMALL((SMALL((COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))ISTEXT(\$D\$5:\$K\$23))),ROW(\$1:\$152))(ROW(\$1:\$152)<ROWS(\$D\$5:\$K\$23)*COLUMNS(\$D\$5:\$K\$23))),ROW(\$1:\$152)))),””),ROWS(\$1:1))=(COUNTIF(\$D\$5:\$K\$23,”<=”&\$D\$5:\$K\$23)+(SUM(–ISNUMBER(\$D\$5:\$K\$23))*ISTEXT(\$D\$5:\$K\$23))))),ROWS(\$D\$5:\$K\$23))/ROWS(\$D\$5:\$K\$23)),””)

Theme wordpress giá rẻ Theme wordpress giá rẻ Thiết kế website