Ranking data and creating a pareto chart with duplicate values

%3CLINGO-SUB%20id%3D%22lingo-sub-2430213%22%20slang%3D%22en-US%22%3ERanking%20data%20and%20creating%20a%20pareto%20chart%20with%20duplicate%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430213%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20how%20to%20overcome%20issues%20with%20ranking%20duplicate%20values%20in%20a%20data%20set%20when%20trying%20to%20generate%20a%20pareto%20chart%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20an%20example%20of%20the%20data%20set%20I%20am%20working%20with.%20I%20have%20used%20the%26nbsp%3BRANK%20function%20to%20order%20the%20data%2C%20however%20when%26nbsp%3Bduplicate%20values%20appear%20the%20VLOOKUP%20function%20will%20only%26nbsp%3Breturn%26nbsp%3Bone%20the%20of%20the%20ranked%20duplicate%20values.%20In%20the%20example%2C%20where%20the%20value%20'30'%26nbsp%3Bappears%20twice%2C%20the%20VLOOKUP%20only%20pulls%20one%20of%20these%20across%20and%20leaves%20the%20other.%20When%20generating%20a%26nbsp%3Bpareto%20chart%20I%20then%20have%20missing%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20here%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EBeth%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20version%3A%202008%3C%2FP%3E%3CP%3EOperating%20system%3A%20Windows%2064-bit%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2430213%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2430330%22%20slang%3D%22en-US%22%3ERe%3A%20Ranking%20data%20and%20creating%20a%20pareto%20chart%20with%20duplicate%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074542%22%20target%3D%22_blank%22%3E%40Bethany1993%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20values%20in%20column%20C%20refer%20to%20another%20workbook%2C%20so%20we%20see%20%23VALUE!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2430847%22%20slang%3D%22en-US%22%3ERe%3A%20Ranking%20data%20and%20creating%20a%20pareto%20chart%20with%20duplicate%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430847%22%20slang%3D%22en-US%22%3EThank%20you%20for%20letting%20me%20know%20Hans%2C%20I%20have%20amended%20and%20uploaded%20the%20file%20again%3C%2FLINGO-BODY%3E
New Contributor

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

@Bethany1993 

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

@Bethany1993 Why bother ranking? Perhaps you can work with a solution like in the attached workbook.

Screenshot 2021-06-09 at 16.54.09.png

@Bethany1993 

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.

@Bethany1993 

 

Hopefully the attached document will help you.

 

You can try here for Microsoft Excel & Office 365.

 

And if you are comfortable with Google Sheets, you can try here.

 

Qaiser_j_0-1631880686740.png

 

Thanks