How to make every number in a range of cells unique???

[Mark McInerney]

Hi,

I have a large range of data - I need to use range formulas using If and Large combinations to return the largest number when certain conditions are met.

As the dataset is large, there are duplicate values.

As a workaround I add a very small number to every number in the range, to create a unique value in every cell in the range.

Is there a better way to achieve this - make every cell in a range unique?

Thanks - Mark.

 

[Toadstool]

Hi Mark,

I'd actually take a different approach and create a column with the distinct rank. You can then use that rank as you unique identifier to find the row of data you want.

Here's my list of numbers (with many duplicates) and I'm just showing the first 14 of 180 entries. The 314 is the largest number but I can make it rank 1,2,3,4 instead of all 1.

	I	J	K
Number	Distinct Rank

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]114[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]94[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]82[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]83[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]27[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]314[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]128[/TD]

</tbody>

Sheet3 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=RANK.EQ(I2,$I$2:$I$181,0)+COUNTIF($I$2:I2,I2)-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


P.S. I'd use the LARGE option of AGGREGATE rather than LARGE as it's faster on large datasets.