use formula rand between without duplicated

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
982
Office Version
  1. 2010
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
 

Some videos you may like

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
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top