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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thank you so much again jindon! Really appreciate it