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:
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.
Bookmarks