Custom Sort



I am having a problem with the sort function after entering a custom list,
and sorting by that list, the sort does not follow my pattern, or the order
of my entries. I have searched previous problems listed in the Discussion
Groups and haven't found my specific problem, however, just a warning, I am
not quite as computer literate as others, so if I give too much info or not
enough, as I don't know how much you need, please forgive. =)

I am using the worksheet to inventory children's clothes. After begining at
Home, I've gone to the Sort & Filter > Custom Sort > Sort by (D): Size, Sort
on: Values, Order: (this is a custom list I previously entered). I must add
that I have over 500 total worksheet entries presently. Approximately a
month ago, I lost this information and had to start from the beginning.
Prior to losing my info, I had sorted in the same way, and that sort DID
follow my value pattern, nevertheless, I had only approximately 100-175
entries at that time. Here is an example of my custom value list and the
order in which I want the info sorted:

0-3m, 0-3-6m, 0-6m, 0-9m, 3m, 3-6m, 6m, 6-9m, 6-12m, 6-12-18m, 9m, 9-12m,
12m, 12-18m, 18m, 18-24m, 24m, 36m, 2T, 2T-3T, 3T, 3, 3/4, 4T, 4/4T, 4, 4-5T,
5T, 6, 7, 8, 10, 12, 01M, 02M, 03M, 04M, 5M, 6M, 7M, 8M, 9M, 10M, 11M, 12M,
2M, 3M, OS, 0-12m, 6-18m, 12-24m, 3-12m, 2-3Y, 4-5Y, 6-7Y, small, medium

This is the order in which the sizes were sorted, also the order shown in
the drop down now that the sort has been performed (as best as I can tell, as
I cannot see every value in the drop down box):

Visible top row in drop down box:

3m*, 3-6m, 6m*, 6-12m, 9m*, 9-12m, 12m*, 18m, 18-24m, 24m...

Visible bottom row in drop down box:

2T, 3T, 2T-5T, 12-24m, 6-18m, 0-12m, OS, 3M, 2M, 12M, 11M, 10M, 9M, 8M...

Post sort order on worksheet, Column D, begins exactly as the Visible top
row in drop down box, EXCEPT where I have indicated by #m*, there is an
interuption by the same number but with the capitol M, (m=month, M=Medium),
sofor example, in the middle of 3 month clothes, I have four pairs of size
3Medium shoes, and this is not acceptable.

After the 24m... from the order in the Visible top row, there is order, but
it is not continuous order, e.g.:

2T, 3T, 3, 4T, 5T, 5, 6, 7, ...12, 01M, 02M, 03M, ...11M, 2M, 12-18m, 13M,
2-3Y, 24m, 2T-3T, 2T-4T, 1,(obviously this is an error on my part in the
"number" format, as I never had a "1"), 3-12m, 3-4Y, 36m, 3T-4T, 4-5Y...

I would like to know what I need to do so my values will sort in the desired
custom order that I previously wrote (and obviously will have to write again).

I apologize if this is excessive information. I appreciate your time and
consideration in this matter, and look forward to your response!

Best Regards,

Gary''s Student

The first step is to create a sort forcing table in two unused columns, say
A1 thru B57:

0-3m 1
0-3-6m 2
0-6m 3
0-9m 4
3m 5
3-6m 6
6m 7
6-9m 8
6-12m 9
6-12-18m 10
9m 11
9-12m 12
12m 13
12-18m 14
18m 15
18-24m 16
24m 17
36m 18
2T 19
2T-3T 20
3T 21
3 22
3/4 23
4T 24
4/4T 25
4 26
4-5T 27
5T 28
6 29
7 30
8 31
10 32
12 33
01M 34
02M 35
03M 36
04M 37
5M 38
6M 39
7M 40
8M 41
9M 42
10M 43
11M 44
12M 45
2M 46
3M 47
OS 48
0-12m 49
6-18m 50
12-24m 51
3-12m 52
2-3Y 53
4-5Y 54
6-7Y 55
small 56
medium 57

The next step is to re-arrange the items in column A to product the desired
sort order.

Let's say the data to be sorted is in columns D thru H where H is the size
code and column I is un-used,

Put a label in I1 and in I2 enter:
=VLOOKUP(H2,$A$1:$B$57,2,FALSE) and copy down

Finally sort cols D thru I by I

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question