# 如何重新分配一定范围内的值而没有1到100之间的重复项？

I have a list of values between 1 and 100, essentially a sort of ranking that occassionally skips a few numbers (for example, the first ten values are `2, 6, 6, 10, 10, 10, 10, 11, 12, 13`). They're ordered ascendingly, so every number will be either higher than or equal to the number above it. Now, I wish to remove all the duplicates from this list while remaining between 1 and 100. So, for example, for the values above, something like `2, 6, 7, 10, 11, 12, 13, 14, 15, 16` would work or `2, 6, 7, 8, 9, 10, 11, 12, 13, 14`. However, the formulas I've tried so far will either go over 100, go under 1, or create circular references. Given the nature of the list, there's very little chance of the amount of values exceeding 100, so if that possibility can be accounted for, it'd be a nice bonus, but it's not required.

ueaque

Please create a named range for all your numbers and name it `Source`. As an alternative, replace the named range `Source` in my formulas below with the sheet address of the range where you have your numbers.

[C2] `=INDEX(Source,MATCH(0,COUNTIF(\$C\$1:\$C1,Source),0))`

[E2] `=LOOKUP(2,1/(COUNTIF(\$E\$1:E1,Source)=0),Source)`