Custom sort not working correctly

I'm on windows 10 running MS Offie 2010.
I am using Custom Sort and one of the sort columns isn't working as it should. I've taken a portion of the original spreadsheet and hand entered a few rows in order to try and solve the problem. Attached is the small spreadsheet that I'm using to troubleshoot.

There are 3 columns. The Custom Sort is 1) Sort on Partner, (small to large), 2) sort on Role, (custom values entered) and 3) sort on last name. It appears that the sort on last name isn't working. For troubleshooting I've changed the sort from A-Z to Z-A and the sort doesn't change. I change the sort on the other 2 columns and the sort order changes as it should. I hand entered the data so there shouldn't be any hidden/funky data. I'm stumped.

Attached is a file with 2 worksheets. One is the raw data is one is my goal. I want all the partners listed together (in any order, just together), sorted by the Last Name of the veteran and the veteran listed first in the pairing. If there is another way to get these results I'm all ears!

Thanks for your help.

If you are doing the sort manually, field by field, I suggest you sort the columns in reverse order of priority. That is, Last Name, Role, then Partner.

maybe like this

custom list:

Formula:

Please Login or Register  to view this content.

In Excel, you can perform a dependent custom sort list by creating multiple custom lists and using them in combination for sorting. Here are the steps:

1. Create a list of values that you want to use as the primary sorting criteria in a separate column or worksheet.
2. Create a list of values that you want to use as the secondary sorting criteria in a separate column or worksheet.
3. Select the column of data that you want to sort.
4. Click on the "Sort" button in the "Data" tab.
5. In the "Sort" dialog box, select the column you want to sort by and then select "Custom List" from the "Order" drop-down list.
6. Click on the "Import" button and select the range that contains the custom list you created in step 1.
7. Click "Add Level" to add a second level of sorting criteria.
8. Select the column you want to sort by for the second level of sorting and then select "Custom List" from the "Order" drop-down list.
9. Click on the "Import" button and select the range that contains the custom list you created in step 2.
10. Click "OK" to close the "Custom Lists" dialog box.
11. Click "OK" again to close the "Sort" dialog box and sort your data.
12. Excel will now sort the data based on your custom lists in the order you specified, with the primary sorting criteria taking precedence over the secondary criteria.

Note: You can add additional levels of sorting criteria by clicking "Add Level" and repeating steps 8 to 10.

Partner	last	Role			last	Role	IF	Partner
1	bevill	veteran			bevill	guardian	1	1
1	bevill	guardian			bevill	veteran	1	1
6	Gabriel	veteran			Spencer	guardian	6	2
6	Spencer	guardian			Gabriel	veteran	6	2
3	Guerra	veteran			Guerra	guardian	3	3
3	Guerra	guardian			Guerra	veteran	3	3
2	jung	veteran			De Santis	guardian	2	4
2	De Santis	guardian			jung	veteran	2	4
4	Lerner	veteran			Lerner	guardian	4	5
4	Lerner	guardian			Lerner	veteran	4	5
5	Thaler	veteran			Lerner	guardian	5	6
5	Lerner	guardian			Thaler	veteran	5	6
expected					result