# Making duplicate values unique?

1. ## Making duplicate values unique?

So I've put quite a few hours into this project, just to find out it doesn't quite work properly. I have a spreadsheet with 30,000 rows and 30 columns, with data. I have another sheet that ranks the data depending on the criteria of a few drop down lists, with a fairly lengthy array formula:

``Please Login or Register  to view this content.``
Sometimes, there are duplicate values in each column. I didn't think that would be a problem because the LARGE function was working fine just picking the top largest value, even if there was a duplicate value below that one. However, apparently that isn't the case with the subsequent INDEX/MATCH functions that retrieve the leftmost column of that value.

So, I can think of one way to make all of the values unique. Simply have another column that is equal to the value I want, plus 0.000000001 times the row number. However, I don't want to double the amount of columns in the data spreadsheet. That would be incredibly slow on my system.

Any suggestions? I vaguely recall doing another project that used RANK of lists with unique values.

2. ## Re: Making duplicate values unique?

This formula will convert duplicated values into unique values, maybe of some help to you
Col A is data
B1 =RANK(A1,A:A,0)+COUNTIF(A\$1:A1,A1)-1
Modification of your formula(s) is sure to be needed.
HTH

3. ## Re: Making duplicate values unique?

Ranking the values to make them unique is not going to work.

Is there any way to change the setting/theme/style of a cell so that it equals itself + (0.0000000000001 times the row number)? Will I absolutely need another column for each row of current column of data I have? Would a vba macro be easier?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1