confused sort range based on names in column

[abdelfattah]

hi experts
I try sorting data based on column B
this is what I want

a.xlsx
	A	B	C	D	E	F	G
7	1	alla1	274	22/05/2006	15	4	9
8	2	alla2	266	01/07/2006	15	3	0
9	3	alla3	275	20/08/2006	15	1	11
10	4	alla4	276	08/02/2007	14	7	23
11	5	alla5	271	20/01/2007	14	8	11
12	6	alla6	266	13/05/2006	15	4	18
13	7	alla7	271	18/06/2006	15	3	13
14	8	alla8	269	29/09/2005	16	0	2
15	9	alla9	276	02/07/2006	15	2	29
16	10	alla10	269	01/07/2006	15	3	0
ss

Cell Formulas
Range	Formula
E7:E16	E7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"y"))
F7:F16	F7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"ym"))
G7:G16	G7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"md"))

but suddenly this is what I got

a.xlsx
	A	B	C	D	E	F	G
7	1	alla1	274	22/05/2006	15	4	9
8	2	alla10	269	01/07/2006	15	3	0
9	3	alla11	275	24/07/2006	15	2	7
10	4	alla12	276	01/06/2006	15	4	0
11	5	alla13	271	25/09/2005	16	0	6
12	6	alla14	272	01/08/2006	15	2	0
13	7	alla15	269	28/09/2006	15	0	3
14	8	alla16	270	02/05/2006	15	4	29
15	9	alla17	271	27/04/2006	15	5	4
16	10	alla18	271	14/01/2006	15	8	17
17	11	alla2	266	01/07/2006	15	3	0
18	12	alla3	275	20/08/2006	15	1	11
19	13	alla4	276	08/02/2007	14	7	23
20	14	alla5	271	20/01/2007	14	8	11
21	15	alla6	266	13/05/2006	15	4	18
22	16	alla7	271	18/06/2006	15	3	13
ss

Cell Formulas
Range	Formula
E7:E22	E7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"y"))
F7:F22	F7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"ym"))
G7:G22	G7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"md"))

how should fix,please?

 

[Joe4]

That is correct alphabertic sorting. Remember, you are sorting alphanumeric values, not numeric ones!
In alphanumeric sorting, it looks at each character individually, so "1" always come before "2", so values like "10", "11", "12", will come before "2".

There are various ways to get the data to sort the way that you want. Here are a few options:

1. Split up your values into two columns, so the "alpha" part is in one column, and the "numeric" part is in another column (as a number and not text), and sort by both these columns.

2. Change the format of your values so that there are always two spaces for the numbers, so values like "alla1" would be re-written as "alla01". Then things would sort the way you want.

3. If your entries all follow the same structure (4 character alpha prefix plus number), use a "helper" column to sort, using a formula that creates entries like mentioned in option two above.
That formula would look something like:

=IF(LEN(B7=5),LEFT(B7,4) & "0" & RIGHT(B7,1),B7)

 

[abdelfattah]

thanks for the suggestions . about OPTION3 it's differnt . not the same structure
actually I wish if can do that as in picture1

 

[Joe4]

actually I wish if can do that as in picture1

I know what you want to do, and I told you why it is not working. Do you understand the issue?
Sorting text or alphanumeric values is NOT the same as sorting totally numeric values.

Have you considered either of the other two options?

 

[abdelfattah]

do you try to say me my way is not possible without use your suggestion?

 

[Joe4]

do you try to say me my way is not possible without use your suggestion?

Not necessarily use my suggestion. There may be other ways of doing (perhaps by creating some Custom sort rule).
But you cannot get it to do what you want using the default sort functionality.
You are going to do something on your end, whether it is change the data, use helper columns, create Custom sorting rules, or use VBA.

 

[abdelfattah]

use helper columns,

I don't prfer this choice . I don't want adding more data in my sheet . I prefer doing directly

create Custom sorting rules, or use VBA.

good choices. may be another body can help if you can't do that .

 

[Joe4]

How about updating your data to add in the missing 0?
We could probably come up with VBA code to automatically add that in there. Then you could sort normally.
Is that a possibility?
If it is, could you post a sampling of your "real" data, so we can see exactly what it looks like, and make sure that the code we create will work on it?
Be sure to include different formats/possibilities that you have.

 

[Joe4]

I have been looking into this more, and I do not thing that a Custom sort would work for you. In a Custom sort, you must provide a complete list of all possible values, and the order you want them sorted in. That is really intended for situations in which you have a limited, defined number of options, such as a drop-down list selection like:
- High
- Medium
- Low
and you want them sorted in that particular order.

I am guessing that you will not be able to pre-define all the possible values you may have, especially if the values contain "counters" on the end (the is no limit to the numbers).

In searching other similar question around the internet, almost all the solutions involved using "helper" columns.

So I think you really just have two options here:
1. Enlist the use of helper columns for sorting purposes (which you can hide, by the way).
2. Fix/update the data, as I described in my previous post.

Note that you can use VBA in conjunction with either of these solutions. You could even have VBA add the helper columns, complete the sort, then delete the helper columns after the sort if finished.
The choice is yours.

 

[abdelfattah]

A.xlsm
	A	B	C	D	E	F	G
5	item	name	sum	date	old
6					year	month	day
7	1	ALI13	266	7/1/2006	15	3	0
8	2	ALI14	272	8/1/2006	15	2	0
9	3	ALI15	269	9/28/2006	15	0	3
10	4	ALI16	270	5/2/2006	15	4	29
11	5	ALI1	274	5/22/2006	15	4	9
12	6	ALI2	271	4/27/2006	15	5	4
13	7	ALI3	267	5/9/2006	15	4	22
14	8	ALI4	271	4/8/2006	15	5	23
15	9	ALI5	269	2/24/2007	14	7	7
16	10	ALI6	269	1/10/2007	14	8	21
17	11	ALI7	275	1/12/2007	14	8	19
18	12	MAHMOUD5	275	8/20/2006	15	1	11
19	13	MAHMOUD6	266	1/4/2007	14	8	27
20	14	MAHMOUD7	271	2/15/2007	14	7	16
21	15	MAHMOUD1	269	1/7/2007	14	8	24
22	16	MAHMOUD2	267	9/3/2006	15	0	28
23	17	MAHMOUD3	276	2/8/2007	14	7	23
24	18	MAHMOUD4	271	1/20/2007	14	8	11
25	19	KALID4	266	5/13/2006	15	4	18
26	20	KALID5	271	6/18/2006	15	3	13
27	21	KALID6	269	9/29/2005	16	0	2
28	22	KALID1	276	7/2/2006	15	2	29
29	23	KALID2	272	8/1/2006	15	2	0
30	24	KALID3	269	10/6/2006	14	11	25
SS

Cell Formulas
Range	Formula
E7:E30	E7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"y"))
F7:F30	F7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"ym"))
G7:G30	G7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"md"))

RESULT should be

1طباعة.xlsm
	A	B	C	D	E	F	G
5	item	name	sum	date	old
6					year	month	day
7	1	ALI1	274	5/22/2006	15	4	9
8	2	ALI2	271	4/27/2006	15	5	4
9	3	ALI3	267	5/9/2006	15	4	22
10	4	ALI4	271	4/8/2006	15	5	23
11	5	ALI5	269	2/24/2007	14	7	7
12	6	ALI6	269	1/10/2007	14	8	21
13	7	ALI7	275	1/12/2007	14	8	19
14	8	ALI13	266	7/1/2006	15	3	0
15	9	ALI14	272	8/1/2006	15	2	0
16	10	ALI15	269	9/28/2006	15	0	3
17	11	ALI16	270	5/2/2006	15	4	29
18	12	KALID1	276	7/2/2006	15	2	29
19	13	KALID2	272	8/1/2006	15	2	0
20	14	KALID3	269	10/6/2006	14	11	25
21	15	KALID4	266	5/13/2006	15	4	18
22	16	KALID5	271	6/18/2006	15	3	13
23	17	KALID6	269	9/29/2005	16	0	2
24	18	MAHMOUD1	269	1/7/2007	14	8	24
25	19	MAHMOUD2	267	9/3/2006	15	0	28
26	20	MAHMOUD3	276	2/8/2007	14	7	23
27	21	MAHMOUD4	271	1/20/2007	14	8	11
28	22	MAHMOUD5	275	8/20/2006	15	1	11
29	23	MAHMOUD6	266	1/4/2007	14	8	27
30	24	MAHMOUD7	271	2/15/2007	14	7	16
SS

Cell Formulas
Range	Formula
E7:E30	E7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"y"))
F7:F30	F7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"ym"))
G7:G30	G7	=IF($D7="","",DATEDIF($D7,DATE(YEAR(NOW()),10,1),"md"))

about a drop-down list selection should be M2