Sorting multiple values in ascending order - Excel 2010

1. Sorting multiple values in ascending order - Excel 2010

Hi there,

I am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).

Any help would be much appreciated.

Mark

2. Re: Sorting multiple values in ascending order - Excel 2010

you are sorting chalk and cheese
1.2kg x 5
1.5kg
10kg
6x395g x 4
395g
are all different how would they sort? they are all text values so they sort as text

3. Re: Sorting multiple values in ascending order - Excel 2010

Hi there,

Many thanks for taking the time to reply.

I have previously tried formatting the column to Text however still couldn't get this to work. Am I missing something?

Thanks again

Mark

4. Re: Sorting multiple values in ascending order - Excel 2010

they sort as text so the order would be
1.2kg X 5
1.5kg
10kg
395g
6 X 395g x 4
Large (1 pc) 200g
sort order from left to right is by
0-9 a-z
how would you want them sorted?

5. Re: Sorting multiple values in ascending order - Excel 2010

Hi Mark,

I had a look at your file there, and must echo martindwilson's comments above - the only suggestion I can make is to type out your info again in a manner which will allow you to manipulate as you please. To that end, I'd suggest a column for weight (which should be either G or KG - not a mixture) and a further column for the factor/number of packets, etc. You could then simply multiply the weight (eg 1.2) by the factor (eg 4 packs) to get a weight of 4.8 - doing this throughout your data would allow you to sort by the overall weight of the pack(s), or by number of packs, etc etc.

I don't mean to dance all over this point, but the way the data has been input in your spreadsheet really doesn't lend itself to any useful manipulation.

Hope this helps.

6. Re: Sorting multiple values in ascending order - Excel 2010

Basically all the values are product options (i.e. the information for drop down menus on a website). The e-commerce platform I am using requires me to put all these in manually and pick the sort order. In this case most of them are all weights so ideally I would be looking for it to begin with the lowest one (70g x 24) and work its way up. Perhaps this is too complicated for Excel...?

Thanks again.

7. Re: Sorting multiple values in ascending order - Excel 2010

Originally Posted by M4RKM
The e-commerce platform I am using requires me to put all these in manually
When you say "manually", do you mean you are typing this data? If so, why not just type it in such a way to make sorting it straightforward?

8. Re: Sorting multiple values in ascending order - Excel 2010

Yes, all the information needs to be entered manually. All the values represent various pack sizes (i.e. 70g x 24 would be 70g cans in an outer of 24). This can't be changed to 1680g.

9. Re: Sorting multiple values in ascending order - Excel 2010

It doesn't need to be changed to 1680g, unless that serves some useful purpose for you. If you enter 70 in one column, and 24 in the next column, you can sort by the first column to get your lowest to highest weight. You can still concatenate the "70" and "24" in a third column, to get 70g x 24, if you want an output like that, but at least you will be able to sort your data.

10. Re: Sorting multiple values in ascending order - Excel 2010

Originally Posted by BB1972
Hi Mark,

I had a look at your file there, and must echo martindwilson's comments above - the only suggestion I can make is to type out your info again in a manner which will allow you to manipulate as you please. To that end, I'd suggest a column for weight (which should be either G or KG - not a mixture) and a further column for the factor/number of packets, etc. You could then simply multiply the weight (eg 1.2) by the factor (eg 4 packs) to get a weight of 4.8 - doing this throughout your data would allow you to sort by the overall weight of the pack(s), or by number of packs, etc etc.

I don't mean to dance all over this point, but the way the data has been input in your spreadsheet really doesn't lend itself to any useful manipulation.

Hope this helps.

Completely understand what your saying about calculating the overall weight then sorting it, however don't think it will work for me because for example 100g x 240 (24000g) would then fall below 15kg (15000)where as I really want it to sort as 100g then 15kg regardless of the pack size at the end.

This is probably why the platform requires the user to select the sort order manually instead of just sorting it itself.

11. Re: Sorting multiple values in ascending order - Excel 2010

So just enter your data as suggested in Post 9! Otherwise - good luck.

12. Re: Sorting multiple values in ascending order - Excel 2010

Originally Posted by BB1972
It doesn't need to be changed to 1680g, unless that serves some useful purpose for you. If you enter 70 in one column, and 24 in the next column, you can sort by the first column to get your lowest to highest weight. You can still concatenate the "70" and "24" in a third column, to get 70g x 24, if you want an output like that, but at least you will be able to sort your data.
Apologies. I have got you now. That might work actually.

Only problem I can think of for example - say the same weight appeared with different pack sizes (100g x 6, 100g x 8 and 100g x 12) If I have split this into two columns - weight in 1 column and pack size in another how can I get it to sort the weight and pack size into ascending order?

Basically stage 1 would be to sort weight (100g) but if a pack size existed (x 6, x 8, x 12) then take this into consideration.

Thanks again.

13. Re: Sorting multiple values in ascending order - Excel 2010

Originally Posted by M4RKM
Only problem I can think of for example - say the same weight appeared with different pack sizes (100g x 6, 100g x 8 and 100g x 12) If I have split this into two columns - weight in 1 column and pack size in another how can I get it to sort the weight and pack size into ascending order?
That's when you use Excel's custom sort to sort by your first column, and then your second.

14. Re: Sorting multiple values in ascending order - Excel 2010

Thanks Brendan! Really appreciate it.

Done a quick test there and think this will work perfectly.

Thanks once again for your help!

15. Re: Sorting multiple values in ascending order - Excel 2010

No problem, Mark - glad to help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1