Forum Discussion
Ranking data and creating a pareto chart with duplicate values
Hello,
Does anyone know how to overcome issues with ranking duplicate values in a data set when trying to generate a pareto chart?
I have attached an example of the data set I am working with. I have used the RANK function to order the data, however when duplicate values appear the VLOOKUP function will only return one the of the ranked duplicate values. In the example, where the value '30' appears twice, the VLOOKUP only pulls one of these across and leaves the other. When generating a pareto chart I then have missing data.
Any help here would be greatly appreciated.
Thank you,
Beth
Excel version: 2008
Operating system: Windows 64-bit
5 Replies
Hopefully the attached document will help you.
You can try here for https://chartexpo.com/utmAction/NCtNVEMreGwrUUorTVNPRlRDTVQr.
And if you are comfortable with Google Sheets, you can try https://chartexpo.com/utmAction/NCtNVEMrZ3MrUUorTVNPRlRDTVQr.
Thanks
The values in column C refer to another workbook, so we see #VALUE!
- Thank you for letting me know Hans, I have amended and uploaded the file again
In the attached version, I added tiny random disturbances to the data. I left the column of random numbers that I used for your information, but it is not needed any more. The disturbances break the ties.
They are hidden by formatting the numbers with 0 decimal places.
