From smallest to largest without repeating

[vacho]

Hello,

I have data consisting of 20 numbers from 1 to 63. Within 20 some numbers can appear twice or even more. I want excel to sort this numbers in smallest to largest format ignoring repeating numbers.

Thank you

 

[patel45]

attach a link to a sample file

 

[L. Howard]

Assuming your data is in a column, select the column, on the ribbon > Data > Data Tools > Remove Duplicates > Sort & Filter > Sort A to Z (down arrow).

HTH
Regards,
Howard

 

[vacho]

Sorry, I forgot to mention that my data are scattered and I want to paste them in one column

 

[Aladin Akyurek]

Hello,

I have data consisting of 20 numbers from 1 to 63. Within 20 some numbers can appear twice or even more. I want excel to sort this numbers in smallest to largest format ignoring repeating numbers.

Thank you

Values	#Sorted#
7	7
59	12
38	15
12	32
36	36
29	38
34	57
15	59
40
58
12
8
38
32
57
7
36
15
27
49

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

B2, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(1-ISNUMBER(MATCH($A$2:$A$21,$B$1:B1,0)),$A$2:$A$21),
  ROWS($B$2:B2)),"")

Is this what you wanted?

 

[Teethless mama]

Values	#Sorted#
7	7
59	12
38	15
12	32
36	36
29	38
34	57
15	59
40
58
12
8
38
32
57
7
36
15
27
49

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

B2, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(1-ISNUMBER(MATCH($A$2:$A$21,$B$1:B1,0)),$A$2:$A$21),
  ROWS($B$2:B2)),"")

Is this what you wanted?

You can shorten up your formula by using "ISNA" instead of "1-ISNUMBER"

=IFERROR(SMALL(IF(ISNA(MATCH(A$2:A$21,B$1:B1,)),A$2:A$21),ROWS(B$2:B2)),"")

 

[Aladin Akyurek]

Sorry, I forgot to mention that my data are scattered and I want to paste them in one column

Do you mean not in one column or in one row?

 

[cnestg8r]

You can shorten up your formula by using "ISNA" instead of "1-ISNUMBER"

=IFERROR(SMALL(IF(ISNA(MATCH(A$2:A$21,B$1:B1,)),A$2:A$21),ROWS(B$2:B2)),"")

.
It appears to me that the following are missing form the suggested solution?:
8
27
29
34
40
49
58

 

[Aladin Akyurek]

.
It appears to me that the following are missing form the suggested solution?:
8
27
29
34
40
49
58

You are right, but it's my fault...

Values	15
7	#Sorted#
59	7
38	8
12	12
36	15
29	27
34	29
15	32
40	34
58	36
12	38
8	40
38	49
32	57
57	58
7	59
36
15
27
49

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

B1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$21<>"",MATCH($A$2:$A$21,$A$2:$A$21,0)),
  ROW($A$2:$A$21)-ROW($A$2)+1),1))

B3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$3:B3)<=$B$1,
  MIN(IF(1-ISNUMBER(MATCH($A$2:$A$21,$B$2:B2,0)),$A$2:$A$21)),"")

By the way:

=IF(ROWS($B$3:B3)<=$B$1,MIN(IF(ISNUMBER(MATCH($A$2:$A$21,$B$2:B2,0)),"",
  $A$2:$A$21)),"")

is okay too.

 

[Teethless mama]

Excel Workbook
	A	B
1	Values	Sort
2	7	7
3	59	8
4	38	12
5	12	15
6	36	27
7	29	29
8	34	32
9	15	34
10	40	36
11	58	38
12	12	40
13	8	49
14	38	57
15	32	58
16	57	59
17	7
18	36
19	15
20	27
21	49
Sheet1