• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

arranging data as per dates of each month

S

SANTHOSH

Member
@ Dear All,

I attach herewith the sample data, which contains various expiry dates. Data is entered in one worksheet and output is required as per the 2nd worksheet. The dates are updated daily /periodically in the worksheet "All data". However, this has to be done in the worksheet "Monthwise" also. The total number of data will be around 200.

The requirement is that, in the worksheet "All data", the data to be extracted in such a way that date and month of each year to be sorted chronologically whenever the dates are updated in the worksheet "All Data"


SANTHOSH
 

Attachments

  • DUMMY DATA_SANTHOSH.xls
    29.5 KB · Views: 18
@Dear Fasheesh,

This is partially Okay. Request you to see my sample file once again. What I want the data to be sorted datewise & monthwise of the respective year with a separate group header. The expiry dates of each item is monitored daily on each month. For example, if name of company is ABC Co Ltd, and having 3 different expiry dates, ie., 18-March, 2014, 15th May, 2014 and 20th April, 2014, name of company will come 3 times in their respective months of that year. Therefore, displaying data of each month is important.

Further, the result provided is in different version of excel. Please covert into 2003 format.


SANTHOSH
 
Hi Santosh,

There are four dates in your file, you want a sorted dated. You did not mention out of four which date you want to sort?

Regards,
 
@ Somendra,

All dates are to be sorted. To be more specific, I will explain this once again. For example, If an Insurance company is dealing with various kinds of vehicles like motor car, scooter, JCB, Tractor Machines, etc for their various clients. They have various expiry dates. So, daily monitoring of all these, for each month in the respective year becomes typical & complex. Whenever data is entered in one worksheet, it is to be sorted datewise in the second worksheet. The expiry dates are not constant and will be changed when it is renewed.

This procedure is to be followed and hope you dont mind in clearing the doubts.


SANTHOSH
 
@SANTHOSH

Let me explain you what I asked. Your file contains dates in column C,D,E and F. With formulas you can sort whole data based on any one column. With macros I think its possible.

But why not you do custom sorting everytime you want to check with multi level sorting.

Regards,
 
@Somendra,

The result is required in a separate worksheet. If Custom sorting on multiple columns is used, each sub header cannot be displayed properly". If expiry dates are renewed after one year, this may also affect a lot.

Attach herewith the screen shot as per company format. I tried a lot but in vain, Could you pls provide any solution. I am not expert in macro.


SANTHOSH
 

Attachments

  • upload_2014-3-11_13-51-31.png
    upload_2014-3-11_13-51-31.png
    55.7 KB · Views: 20
@SANTHOSH

Sorry Brother I think this format can be done with macro only, and I am not good at it. May be you should take help of somebody who is expert in it.

Regards,
 
@Somendra,

Can you convert the data to 2003 format with formula as Faseesh given. Let me try with the formula without group header.

SANTHOSH
 
@ Somendra,

Received the excel file. Functions is also working. But Single column sorting is also not uniform.Let me try with another options. Please bear with me.


SANTHOSH
 
@Somendra,

The function provided is excellent for sorting of data chronologically if it is a single column. i .e only on any of the expiry dates. However, any of the cell in the "All data" worksheet is blank, the corresponding data in "Monthwise" worksheet is displaying "0". How to display it blank only ?


SANTHOSH
 
@Somendra,

This is pretty good and thanks for your smart tricky solution. In fact become a solution for another query.


SANTHOSH
 
Hi SANTHOSH,

This might not be an ideal solution but you can keep record in this format using a pivot table. Please see this:
 

Attachments

  • DUMMY DATA_SANTHOSH.xls
    34.5 KB · Views: 14
@Fasheesh,

Thanks for your feedback. Still, the output will be something like that.

If the name of company is 3 different expiry dates falling on different months, it will display 3 times against respective months and rest columns can be blank also.



SANTHOSH
 
@ Somendra / Fasheesh,

Excuses for taking your precious time. Is there any solution to my query "ARRANGING DATA AS PER DATES OF EACH MONTH"

SANTHOSH
 
Hi Santhosh,

If you are talking about getting the output format in the file you uploaded in your original post consider below:

1. You want all the expiry to be seggregated month wise.
2. For any company there are four expiry dates in different months. So if you say first list the companies with expiry say in march, than only one or 2 or 3 or may be all 4 will have expiry date in march. So you can only sort w.r.t any one date.

How can four different months can be sorted all in march ?

Further to this the pic you uploaded, was putting the values of different vehicle of one company and you had sorted w.r.t. Expiry month 6.

Now this data does not match with you sample file and requirement. So just think what you exactly required and than come back, surely one over here will help you.


Regards,
 
@Somendra,

Sorry for uploading the wrong file. The file containing live data is attached. The worksheet containing "Data" is the live data from where the output is required and Worksheet named "Datewise list" where the result is required.

Different columns of "Data" worksheet is to be arranged as per the format of "Datewise list."

Please confirm if you received file.


SANTHOSH
 

Attachments

  • List of Equipment 19-3-2014- RC.xls
    78.5 KB · Views: 13
Thank you so much. I have been really held up for the last 6 months to get a solution with monthly updates.

If something is worked out, so grateful and thankful to you.

SANTHOSH
 
You must log in or register to reply here.
Back
Top