Sort data without changing the set of each data (using VBA)

[feni1388]

Hello everyone...
I have data that needs to be sorted based on date and its number of occurance.
The user copy and paste one set of data one by one using VBA. So the date is random. Can be 2024/6/12 at the end of the rows or perhaps in the middle.
So I need to sort it first by date and then the number of occurance. But I still can't find a way to sort it without changing the set of each data.
I don't know how to do it manually, let alone using VBA.
Any idea how to do this?
Please help.

Book1
	A	B	C	D	E	F
1	Date	Customer code	No. of items	Item code	Qty	Number of occurance
2	2024/6/14	CHO01018	1	GH07	10	1
3			2	GL78	1
4			3	GH73	1
5	2024/6/13	CMX01027	1	GH07	10	1
6			2	GL78	1
7			3	GH73	1
8			4	GL83	2
9	2024/6/14	CNB01064	1	BM43	15	2
Sheet1

Cell Formulas
Range	Formula
C2,C5,C9	C2	=IFERROR(IF(D2<>"",1,""),"")
C3:C4,C6:C8	C3	=IF(D3="","",IF(AND(D3<>"",C2=""),1,C2+1))
F2:F9	F2	=IF(A2="","",IF(A2=A2,COUNTIF($A$2:A2,A2),""))

I want it to be like below

Book1
	A	B	C	D	E	F
1	Date	Customer code	No. of items	Item code	Qty	Number of occurance
2	2024/6/14	CHO01018	1	GH07	10	1
3			2	GL78	1
4			3	GH73	1
5	2024/6/14	CNB01064	1	BM43	15	2
6	2024/6/13	CMX01027	1	GH07	10	1
7			2	GL78	1
8			3	GH73	1
9			4	GL83	2
Sheet1

Cell Formulas
Range	Formula
C2,C5:C6	C2	=IFERROR(IF(D2<>"",1,""),"")
C3:C4,C7:C9	C3	=IF(D3="","",IF(AND(D3<>"",C2=""),1,C2+1))
F2:F9	F2	=IF(A2="","",IF(A2=A2,COUNTIF($A$2:A2,A2),""))

 

[feni1388]

Ahhh,
This should do.

Sub test()
    Dim a, i&, temp
    Application.ScreenUpdating = False
    a = Range("a2", Range("a" & Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a, 1)
        If a(i, 1) <> "" Then temp = CLng(a(i, 1))
        a(i, 1) = temp + i / (UBound(a, 1) * 100)
    Next
    Columns(1).Insert
    Columns(1).NumberFormat = ""
    [a2].Resize(UBound(a, 1)) = a
    [a1].CurrentRegion.Sort [a1], 1, , , , , , 1
    Columns(1).Delete
    Application.ScreenUpdating = True
End Sub

Thank you so much.... It works great ...
May I know what the problem was? Was it something that I added that cause the sort didn't work as usual?

 

[Fuji]

That was "MySort".

The data you provided initially absolutely coincidentally matched the sort.

So, I just used the method I used in another thread few days ago, and this should give no harm to any formula if used.

HTH.

 

[feni1388]

That was "MySort".

The data you provided initially absolutely coincidentally matched the sort.

So, I just used the method I used in another thread few days ago, and this should give no harm to any formula if used.

HTH.

I see.... thank you for your explanation.