Currently trying to sort data into alphabetical order, however, I am running into a problem.

Every time I try to sort it the product number doesn't match up with the product.

Here is what it looks like unsorted

Here is a picture of it when it is sorted

I have been going on for hours trying to figure this out, but I can't seem to lock the "columns" that have multiple sub-columns to it. It seems as if it is only sorting on a 1 by 1 basis. How would I sort it with multiple columns? For an example the supply "Herculite xRV composite" has multiple product numbers but when sorted the numbers just get placed at the bottom and only the cell next to the product name follows it when sorting.

  • The blanks will always sort to the bottom like that. What you might want to do is make a sort column to the left of your table so that every row can have text but is pretty much duplicating the data but with no blanks. “Herculite xRV composite-1” then “Herculite xRV composite-2” and so on. After your sort the “hide” that column, when you need to sorta again “unhide” the column, etc. – Tyson Aug 4 '18 at 22:05
  • 1
    Use copy and paste to create that sort column identically first, then fill in every blank row, so that you have no blanks at all in that new sort helper column. – Tyson Aug 4 '18 at 22:16
  • Nothing wrong I've found in data after been Sorted. Excel only reshuffles and adjusted blank rows. Check Dentin A1 to A3 and Brush Handle, A3:99:2126 has NO mismatch , were no Supply data in Screen Shot1 and still data less. !! – Rajesh S Aug 5 '18 at 9:20
  • The blank entries under "supply" only have meaning in the original sort order because you can visually see that they are meant as replications of the previously named item. Once you sort into another order, there is nothing to identify what the blanks are supposed to be. They could even become associated with with a different supply. That's why every supply cell should be filled in. If you want to hide repeated values, you can do that with conditional formatting. – fixer1234 Aug 27 '18 at 6:36

Dear OP, My proposed solution will be very similar to @Tyson , Just re-arranging the words... All credit should go to him as the 1st to answer.

  1. Create another 2 columns. one is named "number", and he other one is "Supply2".
  2. [ a ] For the "number" column, fill in 1,2,3... until the last row. [ b ] Copy the content of "Supply" column to "Supply2" .
  3. Fill in the blanks in "Supply2" columns with the correct Supplier name. Make sure you don't leave any black rows.
  4. Select all the column headers, Data > filter.
  5. From the filter @ column headers, select 'sort by color' > 'custom sort'.
  6. Then choose 'Sort by' --> "Supply2", 'A to Z' . 'Add Level' > 'Then by' --> "number", 'A to Z'

Hope that helps.

p/s : You can 'hide' the extra column after you had completed the sort. ( :


If the data is just raw cells it can be difficult or impossible to sort as you describe. If you are selecting a single block of data, Excel will maintain the row association but sort by the highlighted cell.

Example sorting by the first column:
Excel sort first column

Example sorting by the last column:
Excel sort last column

I cannot make it sort by the middle column. However if you format the data as a Table you get a lot more power over sorting:
Excel format as table

Here it is easy to select Column2 and set the sort order. Row data is maintained correctly.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.