use formula rand between without duplicated

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
989
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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,128,207
Messages
5,629,294
Members
416,384
Latest member
frsamiee

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