Sort Data In Chronological Order Of Periods

  • Hello all,

    Firstly i would like to say that I was quite experienced in excel but I forgot most of it by now. My boss came up with a problem that I am struggling quite a bit and I will need to develop a macro in vba for this.

    Basically, it is a bit difficult to explain this but it makes clear sense when you see the attached workbook.

    I will need to sort the data based on some number entries being presented in Periods 1 ,2 ,3 in chronological order.. i.e. move the data of period 1 to the top , then period 2 and then period 3.

    please see the attachment and let me know if you could help.

    Chang Li

  • Re: Use Column Periods To Sort Data

    Well, there are bad spreadsheet designs and then there are horrible designs. Want to guess which one yours is?

    Please take a look at this page and re-think your layout. It needs to be in a database style structure.

    Efficient Spreadsheet Design

  • Re: Sort Data In Chronological Order Of Periods

    AAE, i appreciate your comment but this problem is not designed to be a database.

    It is designed in this way to appeal to users and I require a solution to this problem as it stands.

    I am sure a smart macro could make this happen :D


  • Re: Sort Data In Chronological Order Of Periods


    this problem is not designed to be a database

    OK. But is still needs to be in a different structure, which should be a single top row with column headers and no blank rows inserted between the data, and NO merged cells as you currently have.

    Sorting in chronological order is a "no brainer", but the way you've got things laid out turns this from a "mole hill" into a mountain.

    The forum is here to help, but we do expect everyone to help themselves as well, which means if you have an improper data structure you take a serious look at how it can be approached differently.

    Why do you have two columns of each "Period" category? Excel only allows sorting on up to three columns and with double columns of data and two "Text" columns that need to be included there is NO WAY the relative relationships can be maintained because you will have to perform the sort operation more than one time and it will change the previous results for the "Text" columns.


    It is designed in this way to appeal to users

    If your raw data are on a "data" sheet and properly laid out, then create another sheet to extract and sort the values you want and ,optionally, a third sheet the present it in a visually appealing manner. You first have to start with approach with the data before you go further.:soapbox: :smile:

  • Re: Sort Data In Chronological Order Of Periods

    Have a play with the following and see if you cant tweak it to do as you want. (It doesn't replicate what is on the 'After Macro' sheet but it may fit your 'appeal to users' criterion):


  • Re: Sort Data In Chronological Order Of Periods

    AAE, I agree there shouldn't be any merged cells.. and there must be two columns for each period..

    p45cal, I had a quick test of your macro and it appears to be brilliant, just what I wanted! I will try and tailor it ..

    thanks to both of you for replying :-)[hr]*[/hr] Auto Merged Post;[dl]*[/dl]actually, when I have tried this macro on other sets of data I didn't get the desired results.

    Therefore I have came up with an idea.

    I have now 2 extra columns:

    Column O will determine the size of the data entry

    Column Q will determine the period which the data entry should be allocated to

    Now I just need a macro which will sort by Column Q whilst retaining the sizes of the data entries per Column O and the spaces in between data entries.

    Entry # 5, deliberately has two period allocations. This is to show that I would like Period 1 to take precedence over Period 2 etc..

    Hopefully this would make things easier.

    thanks again in advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!