Related Posts
How much do you put into TSP?
Pro
What is the salary for a ‘H4’ band?
Additional Posts in Excel Genius
Still don't have xlookup. #fomo
Would anyone be willing to teach me vlookup?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
So if you want to eliminate those “duplicates” in your dataset, with a helper column you Might be able to shoehorn in the UNIQUE function.
How would you rank one of the same ratings over the other?
Would use helper columns to write this logic. I’d give like a .0000000001 for each lead so it doesn’t really impact conversion %, only the ranking equation
One problem is that if you rank 2 of the same, you need to knock the next value down a rank and more for each instance regardless of how you rank a duplicate.
Only way I can think of is to not use Rank and do it in PQ and then lookup up the rank.
In PQ, load the table in using Excel.CurrentWorkbook and then remove all columns except the unique reference and the column to be ranked.
Sort by ranking preference. Ascending or descending. Then add a index column (starting from 0 or 1 and increasing increments of 1).
Load the table back in and then lookup the unique reference to the index.
This should work off the top of my head but you need to refresh the table when new values are added, use a table to store data.
But it will rank by criteria regardless of the row order and will work without needing to sort anything outside of PQ.
Doing it dynamically isn't impossible but overly difficult imo.
the only thing you can do is identify and add a .1 if duplicated. its one of the shortcomings of the formula