Sorting issue

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm trying to sort this trimester based information. The desired output is in the second table, ie Tri 1 2017, Tri 2 2017, Tri 3 2017 etc, in other words chronological order. Even with a custom sort I cannot get the result on the right.

Book10
ABCDE
1PeriodSubjectPeriodSubject
2Tri 1 2017FrenchTri 1 2017Maths
3Tri 1 2017MathsTri 2 2017Geog
4Tri 2 2017EnglishTri 3 2017Hist
5Tri 2 2017GeogTri 1 2018French
6Tri 3 2017HistTri 2 2018English
7Tri 3 2017ScienceTri 3 2018Science
8Tri 1 2018FrenchTri 1 2019Maths
9Tri 1 2018MathsTri 2 2019Geog
10Tri 2 2018EnglishTri 3 2019Hist
11Tri 2 2018GeogTri 1 2017French
12Tri 3 2018HistTri 2 2017English
13Tri 3 2018ScienceTri 3 2017Science
14Tri 1 2019FrenchTri 1 2018Maths
15Tri 1 2019MathsTri 2 2018Geog
16Tri 2 2019EnglishTri 3 2018Hist
17Tri 2 2019GeogTri 1 2019French
18Tri 3 2019HistTri 2 2019English
19Tri 3 2019ScienceTri 3 2019Science
Sheet1
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
If you have a limited number of possibilites (which it appears you might), you can create your own list for Custom Sort, as shown here:

Another way would be to make use of a "helper" column, and sort by that.
So for the value in cell A2, here is the formula for the "helper" column:
Excel Formula:
=RIGHT(A2,4)&MID(A2,5,1)
 
Upvote 0
Greetings
Feedback is highly appreciated

THX

VBA Code:
'Option Explicit
Public Sub Sorting_issue()
Dim Rng As Range, SrtRng As Range
Dim Txt As String
Dim StrtRw As Long, EndRw As Long, N As Long, T As Long, X As Long, I As Long
Dim ArrTxt As Variant
Set SrtRng = Range("A2:A19")
Txt = ""
StrtRw = SrtRng.Cells(1, 1).Row
EndRw = SrtRng.Cells(SrtRng.Cells.Count, 1).Row
For T = 2 To 1 Step -1
    For X = StrtRw To EndRw
    Set Rng = Cells(X, 1)
    If ((InStr(1, Rng.Value, 2017, vbTextCompare) <> 0 Or InStr(1, Rng.Value, 2019, vbTextCompare) <> 0) And InStr(1, Rng.Value, "Tri 3", vbTextCompare) <> 0) Or (InStr(1, Rng.Value, 2018, vbTextCompare) <> 0 And InStr(1, Rng.Value, "Tri 3", vbTextCompare) = 0) Then
    If T = 1 Then
    N = 2
    Else
    N = 1
    End If
    Else
    N = T
    End If
        If WorksheetFunction.CountIf(Range("A2:A" & X), Rng) = N Then
        Txt = Txt & Rng & ";" & Rng.Offset(0, 1) & vbNewLine
        End If
    Next
Next
ArrTxt = Split(Txt, vbNewLine)
For I = LBound(ArrTxt) To UBound(ArrTxt)
    Cells(I + StrtRw, 4).Resize(1, 2) = Split(ArrTxt(I), ";")
Next
End Sub


Book1
ABCDE
1PeriodSubjectPeriodSubject
2Tri 1 2017FrenchTri 1 2017Maths
3Tri 1 2017MathsTri 2 2017Geog
4Tri 2 2017EnglishTri 3 2017Hist
5Tri 2 2017GeogTri 1 2018French
6Tri 3 2017HistTri 2 2018English
7Tri 3 2017ScienceTri 3 2018Science
8Tri 1 2018FrenchTri 1 2019Maths
9Tri 1 2018MathsTri 2 2019Geog
10Tri 2 2018EnglishTri 3 2019Hist
11Tri 2 2018GeogTri 1 2017French
12Tri 3 2018HistTri 2 2017English
13Tri 3 2018ScienceTri 3 2017Science
14Tri 1 2019FrenchTri 1 2018Maths
15Tri 1 2019MathsTri 2 2018Geog
16Tri 2 2019EnglishTri 3 2018Hist
17Tri 2 2019GeogTri 1 2019French
18Tri 3 2019HistTri 2 2019English
19Tri 3 2019ScienceTri 3 2019Science
Sheet3
 
Upvote 0
Hi Joe and Dossfm,

Thanks for your replies, I did try the Custom Sort but couldn't quite get it work. The macro seemed to have no effect.
The helper column has given me the best result.
 
Upvote 0
what is wrong ? the result is same on right col.
I had it set up as Tri 1 2017, Tri 2 2017, Tri 3 2017, Tri 1 2018.
Ideally what I need is to be able to chronologically assess performance during a period of time for each unit so that:
Tri 1 2017 Maths
Tri 2 2017 Maths
Tri 3 2017 Maths
Tri 1 2018 Maths
Tri 1 2017 Eng
Tri 2 2017 Eng
etc.

I realise now that the mock data I in my original post may not have represented this clearly.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top