use formula rand between without duplicated

[abdelfattah]

hello
i try use this function to get the values in column a to col b without any duplicated
this is what i got in col b

Microsoft Excel ‫‬.xlsx
	A	B
1	duplicted	not duplicated
2	10	9
3	20	8
4	20	8
5	10	9
6	52	6
7	3	12
8	2	13
9	5	11
10	88	4
11	90	2
12	100	2
13	88	5
14	90	2
Sheet1

Cell Formulas
Range	Formula
B2:B14	B2	=1+RANK.EQ(A2,$A$2:$A$14)+COUNTIF($A$2:A2,A3)-1

the right result

Microsoft Excel ‫‬.xlsx
	A	B
1	duplicted	not duplicated
2	10	2
3	20	3
4	20	5
5	10	10
6	52	20
7	3	52
8	2	88
9	5	90
10	88	100
11	90
12	100
13	88
14	90
Sheet1

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D
1	duplicted	Rank not duplicated	Value not duplicated
2	10	9	2
3	20	7	3
4	20	8	5
5	10	10	10
6	52	6	20
7	3	12	52
8	2	13	88
9	5	11	90
10	88	4	100
11	90	2
12	100	1
13	88	5
14	90	3
15
Sheet10

Cell Formulas
Range	Formula
B2:B14	B2	=RANK(A2,A$2:A$14)+COUNTIF(A$2:A2,A2)-1
C2:C14	C2	=IFERROR(INDEX(A:A,MOD(SMALL(IF(MATCH(A$2:A$14,A$2:A$14,)=ROW(A$2:A$14)-1,A$2:A$14*10^4+ROW(A$2:A$14)),ROW(A1)),10^4)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shaowu459]

Office 365 solution will be much more easier:

MrExcel.xlsx
	A	B	C
1	duplicted	Value not duplicated
2	10	2
3	20	3
4	20	5
5	10	10
6	52	20
7	3	52
8	2	88
9	5	90
10	88	100
11	90
12	100
13	88
14	90
Sheet10

Cell Formulas
Range	Formula
B2:B10	B2	=SORT(UNIQUE(A2:A14),,1)
Dynamic array formulas.

 

[abdelfattah]

thanks shaowu it works

 

[shaowu459]

thanks shaowu it works

you are welcom