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]

I understand your question/problem and understand what your desired result is.
I need you to tell me which solution option you would like us to help you incorporate!

At the end of my last post, I said:

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.

So which one would you like us to pursue?
Note that if we go with option 2, all your values like: "ALI1", "AL2", "AL3" will be permanently changed to "AL01", "AL02", "AL03".
Are you OK with that?

If not, then I think enlisting the use of helpers columns is the only way to go. We can hide them, or even use VBA to delete them after the sort, if you like.

I just need you to tell me which option you would like to go with.

 

[abdelfattah]

If not, then I think enlisting the use of helpers columns is the only way to go. We can hide them, or even use VBA to delete them after the sort, if you like.

yes

 

[Joe4]

yes

I said that in using the Helper Columns, we could HIDE them or DELETE them afterwards.
Which one do you want to do (HIDE or DELETE)?

 

[abdelfattah]

I would hide because if i add a new name the code doesn't work for helper column when delete . am I right or wrong?

 

[Joe4]

OK, let's start with a non-VBA approach with helper columns, and work from there.

So, we want to set up two helper columns let's use H and I.

Since our first row of data is in cell B7, place this formula in cell H7 and copy down for all rows:

=IF(ISERROR(RIGHT(B7,2)+0),LEFT(B7,LEN(B7)-1),LEFT(B7,LEN(B7)-2))

Likewise, place this formula in cell I7 and copy down for all rows:

=IF(ISERROR(RIGHT(B7,2)+0),RIGHT(B7,1),RIGHT(B7,2))+0

Now, you can sort your data, using BOTH columns H and I in your sort (H is the primary sort column and column I is the secondary one).
One you are done sorting, you can hide both columns H and I.

 

[abdelfattah]

can you show me how the code should be after add the formulas,please?

 

[Joe4]

I am not sure what you mean. Do you mean what the sort looks like?
It looks something like this:

Note that even when columns H and I are hidden, you can still sort by it.

 

[abdelfattah]

I thought your formula support my code in OP. do you mean sort manually?

 

[Joe4]

I thought your formula support my code in OP. do you mean sort manually?

I don't think we are on the same page here. You don't have any "code" in your OP, only Excel formulas ("code" is usually sort for "VBA code").
Are you saying that you want VBA code to automatically add these helper columns and do the sorting for you?

 

[abdelfattah]

I don't think we are on the same page here. You don't have any "code" in your OP

Oh MGH ! my apologies
this is my code automatically when add the names in column B should sort

Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Range("b7:g" & lastrow).Sort key1:=Range("b7:b" & lastrow), order1:=xlAscending, Header:=xlNo
End If
End Sub

Are you saying that you want VBA code to automatically add these helper columns and do the sorting for you?

yes