Latest Lists

Excel: Sorting a list, largest to smallest number accompanied by name. How do I avoid issues with duplicates?

I have two columns: Column A: Column B: Bob..............643 Doug............765 Mckenzie......243 Bob..............856 Doug............453 Mckenzie.....453 Bob.............345 Doug...........234 Mckenzie....756 Bob............978 Doug..........3452 Mckenzie....457 In order to sort the numbers from largest to smallest, I use the formula: =LARGE($B:$B,ROWS($1:1)) In order to ensure I have the correct name with the corect number after the list sorts, I use: =INDEX($A:$A,MATCH($D1,$B:$B,0)) With that I get this: Column C: Column D: Doug............3452 Bob..............978 Bob..............856 Doug............765 Mckenzie.....756 Bob.............643 Mckenzie.....457 Doug...........453 Doug...........453 Bob.............345 Mckenzie.....243 Doug...........234 Notice Doug appearing twice with the number 453 Where Mckenzie should appear in place of one of those Doug's. Now keep in mind this is an example, and the actual list I intend this for contains anywhere between 50-100 unique customers. I had looked into ways to convert words into numbers, but as far as I can find, I would have to assign numbers to words or letters one at a time, and that is not practical. What I was hoping to find is a built in function in excel that assigns a number to a letter so I could get a value from each name and use that value to slightly manipulate each number to ensure there would be no true duplicates. For example: If excel had said built in values for each letter, I could convert Doug into a number and change 453 to 453.00000001 using some equation of sorts, and Mckenzie would be 453.000002, keeping both numbers appear the same, but are not and will show up accordingly in the list above. Anyhow, that was what I was looking to do to no success. If there are any other means to achieve the desired results from above I would appreciate the help. Thanks! I am using the formulas to sort as it is all part of a grand scheme :)... additionally I am trying to figure out how the helper column is going to avoid the issue in columns C and D. Thanks

Public Comments

1. You could just use a 3rd helper column in which you'll create unique values by concatenting the values in A and B. =A2&"."&B2 (assuming you've got column headers, adjust the numbers accordingly) Put that in C2 and fill down. Now you can use column C to get rid of dupes. Nice 'n simple.
2. I wonder why you are using formulas to sort? Why not just select both columns and sort by Col B? If the contents are appearing from a INDEX/MATCH- then copy the two columns and Paste Special values only. Use your copied columns to sort by number.