# use formula rand between without duplicated

#### abdelfattah

##### Well-known Member
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
AB
1duplictednot duplicated
2109
3208
4208
5109
6526
7312
8213
9511
10884
11902
121002
13885
14902
Sheet1
Cell Formulas
RangeFormula
B2:B14B2=1+RANK.EQ(A2,\$A\$2:\$A\$14)+COUNTIF(\$A\$2:A2,A3)-1

the right result
Microsoft Excel ‫‬.xlsx
AB
1duplictednot duplicated
2102
3203
4205
51010
65220
7352
8288
9590
1088100
1190
12100
1388
1490
Sheet1

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### shaowu459

##### Well-known Member
MrExcel.xlsx
ABCD
1duplictedRank not duplicatedValue not duplicated
21092
32073
42085
5101010
652620
731252
821388
951190
10884100
11902
121001
13885
14903
15
Sheet10
Cell Formulas
RangeFormula
B2:B14B2=RANK(A2,A\$2:A\$14)+COUNTIF(A\$2:A2,A2)-1
C2:C14C2=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

##### Well-known Member
Office 365 solution will be much more easier:
MrExcel.xlsx
ABC
1duplictedValue not duplicated
2102
3203
4205
51010
65220
7352
8288
9590
1088100
1190
12100
1388
1490
Sheet10
Cell Formulas
RangeFormula
B2:B10B2=SORT(UNIQUE(A2:A14),,1)
Dynamic array formulas.

#### abdelfattah

##### Well-known Member
thanks shaowu it works

Replies
3
Views
118
Replies
5
Views
109
Replies
5
Views
128
Replies
0
Views
76
Replies
2
Views
132

1,127,401
Messages
5,624,520
Members
416,032
Latest member
dunhamsasphalt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back