Help to Sort Data Chronologically

Hello and welcome to the forum.

Highlight all of your data > Data > Sort > Sort by year Oldest to Newest > OK

Hello, please refer to the first two images attached. I require it to be arranged according to the code 22, 30, 32, 34... in separate identical tables as shown in the second image. Is there any VBA code to sort it like that?

I recommend uploading a small representative sample of your data (in the form of an Excel workbook, not screenshots) along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

While I can't help with VBA, I am confident that I can offer a solution that does not require it.

I have attached the file.

How about a Pivot Table?

All that you have to do is change the filter to the Commodity Code that you want to see.

See attachment.

To create this, highlight your data > Insert > Pivot Table > Drag Year under ROWS, Value under VALUES, and Commodity Code under FILTERS

To display all years, right-click on any of the years in the Pivot Table > Field Settings > Layout & Print > Check "Show items with no data" > OK

You can hide the Grand Total row if you would like as well.

Is it possible to do it automatically for other codes? Because there are like 40 other codes, and 28 more data sets

All that you have to do is change the filter to the commodity code that you want.

If you don't want to have to do that, here is a quick way to get all of the values for all of the years for each Commodity Code.

First, list the years in E2:E18 and the Commodity Codes in F1:AU1. Then, you can use this in F2:
=IFERROR(1/(1/SUMIFS($C:$C,$A:$A,$E2,$B:$B,F$1)),"")
Drag the formula to the right and down.

See attachment.

That's great, But, please see the Sheet 2 in the file, I want it to be arranged vertically as shown for all the Commodity Codes. Will that be possible? Kind of like transpose what you have done.

Hello jaslake, sorry for the confusion. I have uploaded the corrected Sheet 2.
All tables must have years 2000 till 2017. The Pivot table is a great help.
Please advise how to automate the data accordingly in vertical manner.

Try

Sub test()
    Dim a, i As Long, ii As Long, iii As Long, iv As Long, n As Long
    Dim myMin As Long, myMax As Long, SL As Object
    Application.ScreenUpdating = False
    With Sheets("sheet1").Cells(1).CurrentRegion
        a = .Value
        myMin = Application.Min(.Columns(1))
        myMax = Application.Max(.Columns(1))
    End With
    Set SL = CreateObject("System.Collections.SortedList")
    For i = 2 To UBound(a, 1)
        If Not SL.Contains(a(i, 2)) Then
            Set SL(a(i, 2)) = CreateObject("Scripting.Dictionary")
        End If
        SL(a(i, 2))(a(i, 1)) = Application.Index(a, i, 0)
    Next
    With Sheets("sheet2")
        .Columns("a:c").Clear: n = 4
        For i = 0 To SL.Count - 1
            ReDim a(1 To myMax - myMin + 3, 1 To 3)
            a(1, 2) = "Export": iii = 2
            a(2, 1) = "Period": a(2, 2) = "Code": a(2, 3) = "Trade Value"
            For ii = myMin To myMax
                iii = iii + 1: a(iii, 1) = ii
                If SL.GetByIndex(i).exists(ii) Then
                    For iv = 1 To 3
                        a(iii, iv) = SL.GetByIndex(i)(ii)(iv)
                    Next
                End If
            Next
            With .Cells(n, 1).Resize(UBound(a, 1), 3)
                .Value = a: .Borders.Weight = 2
                With .Rows("1:2")
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
                    With .Cells(1, 2).Resize(, 2)
                        .Interior.Color = 5296274
                        .Merge
                    End With
                End With
                .Columns(3).NumberFormat = """$""#,##0"
            End With
            n = n + myMax - myMin + 5
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Thank you so much! I tried using Show Report Filter Pages from Pivot Table.
However, it does not include Commodity Code inside the table as I want. It's outside with the filter tab.
And also that the tables appear in separate worksheets, when I want them to be consolidated into single worksheet.
Hope to hear from you soon! Thanks!

Hello jindon, I'll try to code right away!

vba bug.jpg

Hello jindon, I can't run the code.
A bug appears as shown in the photo.

Does that appear when you run the code with the file I attached?

vba bug 2.jpg

Yes, it shows as attached.

OK, your profile says Excel 2013, is that correct?

Excel 2016

For Mac or Win?

And change the profile immediately.

Windows, what profile to change?

1) Your member profile.
Click on your user name on the top next to "Welcome", you can edit your profile.

2) change to

Sub test()
    Dim a, i As Long, ii As Long, iii As Long, iv As Long, n As Long
    Dim myMin As Long, myMax As Long, dic As Object
    Application.ScreenUpdating = False
    With Sheets("sheet1").Cells(1).CurrentRegion
        a = .Value
        myMin = Application.Min(.Columns(1))
        myMax = Application.Max(.Columns(1))
    End With
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(a, 1)
        If Not dic.exists(a(i, 2)) Then
            Set dic(a(i, 2)) = CreateObject("Scripting.Dictionary")
        End If
        dic(a(i, 2))(a(i, 1)) = Application.Index(a, i, 0)
    Next
    With Sheets("sheet2")
        .Columns("a:c").Clear: n = 4
        For i = 0 To dic.Count - 1
            ReDim a(1 To myMax - myMin + 3, 1 To 3)
            a(1, 2) = "Export": iii = 2
            a(2, 1) = "Period": a(2, 2) = "Code": a(2, 3) = "Trade Value"
            For ii = myMin To myMax
                iii = iii + 1: a(iii, 1) = ii
                If dic.items()(i).exists(ii) Then
                    For iv = 1 To 3
                        a(iii, iv) = dic.items()(i)(ii)(iv)
                    Next
                End If
            Next
            With .Cells(n, 1).Resize(UBound(a, 1), 3)
                .Value = a: .Borders.Weight = 2
                With .Rows("1:2")
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
                    With .Cells(1, 2).Resize(, 2)
                        .Interior.Color = 5296274
                        .Merge
                    End With
                End With
                .Columns(3).NumberFormat = """$""#,##0"
            End With
            n = n + myMax - myMin + 5
        Next
    End With
    Application.ScreenUpdating = True
End Sub

1) Done, thank you for notifying me on that.
2) It worked! Thank you so much!! Will this same code be transferable to another data set, like the one I attached?
If changes are required, please advise.

Have you tried for yourself?

It is working for me.

YES, it is working!! I missed a small thing though, the years should be from 2000 till 2017. Even if there is no data for 2017.
Can that be adjusted into the code?
I did it manually for the first data as attached.
Thank you again!

Just change

        myMax = Application.Max(.Columns(1))

to

        myMax = Year(date) - 1

Thank you so much again jindon! Really appreciate it