How exactly do I make multiple columns into one row to sort?

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.

NaritaDogFight

Posted 2018-08-04T21:34:56.277

Reputation: 1

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 – 2018-08-04T22:05:35.900

1Use 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 – 2018-08-04T22:16:00.350

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:2126has NO mismatch , were no Supply data in Screen Shot1 and still data less. !! – Rajesh S – 2018-08-05T09:20:07.930

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 – 2018-08-27T06:36:43.543

Answers

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.

Create another 2 columns. one is named "number", and he other one is "Supply2".
[ a ] For the "number" column, fill in 1,2,3... until the last row. [ b ] Copy the content of "Supply" column to "Supply2" .
Fill in the blanks in "Supply2" columns with the correct Supplier name. Make sure you don't leave any black rows.
Select all the column headers, Data > filter.
From the filter @ column headers, select 'sort by color' > 'custom sort'.
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. ( :

p._phidot_

Posted 2018-08-04T21:34:56.277

Reputation: 948

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: