Excel Help

[Dnyan]

Hi,

I am daily updating claims as per Renewal Date. It means every time renewal date will be change.
For example.
If Renewal date is 10/03/2011 then we add the claims as per renewal date like: 10/03/2011 to 09/03/2012.
If the Renewal date is change to 05/04/2011 then we add the claims as per renewal date like: 05/04/2011 to 04/04/2012.

So, I want to sort date in another sheet as per Renewal Date for yearly period.

data is like:


<TABLE dir=ltr borderColor=#03e300 cellSpacing=2 cellPadding=2 width=475 border=1><TBODY><TR><TD width="19%" bgColor=#99cc00 height=7>

DATE​

​

​

​

​

​

</TD><TD width="25%" bgColor=#99cc00 height=7>

Paid​

​

​

​

​

​

</TD><TD width="26%" bgColor=#99cc00 height=7>

O / S​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99cc00 height=7>

Total​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

06-May-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

5,015.00​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

5,015.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

23-Jul-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

2,439.00​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

2,439.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

22-Dec-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

15,000.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

15,000.00​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

23-Mar-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

8,468.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

8,468.00​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

02-Sep-07​

​

​

​

​

​

</TD><TD width="25%" height=7>

332,022.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

332,022.00​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

01-Apr-08​

​

​

​

​

​

</TD><TD width="25%" height=7>

345.00​

​

​

​

​

​

</TD><TD width="26%" height=7>

17,155.00​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

17,500.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

21-Jan-09​

​

​

​

​

​

</TD><TD width="25%" height=7>

776.00​

​

​

​

​

</TD><TD width="26%" height=7>

10,000.00​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

10,776.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

15-Apr-09​

​

​

​

​

​

</TD><TD width="25%" height=7>

17,110.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

17,110.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

14-Sep-09​

​

​

​

​

​

</TD><TD width="25%" height=7>

979.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

979.00​

​

​

​

​

​

</TD></TR></TBODY></TABLE>

Regards
Dnyan

 

[yogeshmaney]

what do u want to sum up?

 

[Dnyan]

Hi,

I dont want to sum I want to sort the claims date as per Renewal Date.

<TABLE dir=ltr borderColor=#05e300 cellSpacing=2 cellPadding=2 width=478 border=1><TBODY><TR><TD colSpan=4 height=7>

If Renewal date is 10/03/2011 then sorting will as per below:​

​

​

​

​

</TD></TR><TR><TD width="44%" colSpan=2 height=7>

Claims for the Period​

​

​

​

​

​

</TD><TD width="26%" height=7>

10/03/2006​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2007​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>

10/03/2007​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2008​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>

10/03/2008​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2009​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>

10/03/2009​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2010​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>

10/03/2010​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2011​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="44%" colSpan=2 height=7>

Claims for the Period​

​

​

​

​

​

</TD><TD width="26%" height=7>

10/03/2006​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2007​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#99cc00 height=7>

DATE​

​

​

​

​

​

</TD><TD width="25%" bgColor=#99cc00 height=7>

Paid​

​

​

​

​

​

</TD><TD width="26%" bgColor=#99cc00 height=7>

O / S​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99cc00 height=7>

Total​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

06-May-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

5,015.00​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

5,015.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

23-Jul-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

2,439.00​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

2,439.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

22-Dec-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

15,000.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

15,000.00​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

23-Mar-06​

​

​

​

​

​

</TD><TD width="25%" height=7>

8,468.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

8,468.00​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="44%" colSpan=2 height=7>

Claims for the Period​

​

​

​

​

​

</TD><TD width="26%" height=7>

10/03/2007​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2008​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#99cc00 height=7>

DATE​

​

​

​

​

​

</TD><TD width="25%" bgColor=#99cc00 height=7>

Paid​

​

​

​

​

​

</TD><TD width="26%" bgColor=#99cc00 height=7>

O / S​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99cc00 height=7>

Total​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

02-Sep-07​

​

​

​

​

​

</TD><TD width="25%" height=7>

332,022.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

332,022.00​

​

​

​

​

​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="44%" colSpan=2 height=7>

Claims for the Period​

​

​

​

​

​

</TD><TD width="26%" height=7>

10/03/2008​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2009​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#99cc00 height=7>

DATE​

​

​

​

​

​

</TD><TD width="25%" bgColor=#99cc00 height=7>

Paid​

​

​

​

​

​

</TD><TD width="26%" bgColor=#99cc00 height=7>

O / S​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99cc00 height=7>

Total​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

01-Apr-08​

​

​

​

​

​

</TD><TD width="25%" height=7>

345.00​

​

​

​

​

​

</TD><TD width="26%" height=7>

17,155.00​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

17,500.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

21-Jan-09​

​

​

​

​

​

</TD><TD width="25%" height=7>

776.00​

​

​

​

​

</TD><TD width="26%" height=7>

10,000.00​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

10,776.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="44%" colSpan=2 height=7>

Claims for the Period​

​

​

​

​

​

</TD><TD width="26%" height=7>

10/03/2009​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2010​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#99cc00 height=7>

DATE​

​

​

​

​

​

</TD><TD width="25%" bgColor=#99cc00 height=7>

Paid​

​

​

​

​

​

</TD><TD width="26%" bgColor=#99cc00 height=7>

O / S​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99cc00 height=7>

Total​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

15-Apr-09​

​

​

​

​

​

</TD><TD width="25%" height=7>

17,110.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

17,110.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#ccffcc height=7>

14-Sep-09​

​

​

​

​

​

</TD><TD width="25%" height=7>

979.00​

​

​

​

​

​

​

​

​

​

​

</TD><TD width="26%" height=7>

-​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99ccff height=7>

979.00​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="44%" colSpan=2 height=7>

Claims for the Period​

​

​

​

​

​

</TD><TD width="26%" height=7>

10/03/2010​

​

​

​

​

​

</TD><TD width="30%" height=7>

09/03/2011​

​

​

​

​

​

</TD></TR><TR><TD width="19%" bgColor=#99cc00 height=7>

DATE​

​

​

​

​

​

</TD><TD width="25%" bgColor=#99cc00 height=7>

Paid​

​

​

​

​

​

</TD><TD width="26%" bgColor=#99cc00 height=7>

O / S​

​

​

​

​

​

</TD><TD width="30%" bgColor=#99cc00 height=7>

Total​

​

​

​

​

​

</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR><TR><TD width="19%" height=7>
</TD><TD width="25%" height=7>
</TD><TD width="26%" height=7>
</TD><TD width="30%" height=7>
</TD></TR></TBODY></TABLE>

Regards
Dnyan

 

[Dnyan]

Hi Everone,

Till date I do not received any replied.

Please help....

Regards
Dnyan

 

[Dnyan]

Hi,

Till I do not received any reply.

i have a excel file in which I can sort claim month wise but I want sorting of claims by date wise.

Please check my other excel file in below link and try to solve my query.

http://www.mediafire.com/?mzbaxcc3zztz514http://www.mediafire.com/?mzbaxcc3zztz514

But I want sort this cliam by Renewal date

Thanks & Regards
Dnyan

 

[Dnyan]

Hi,

Can any one help

i have a excel file in which I can sort claim month wise But I want sort this cliam by Renewal date.

like, if Renewal date is 20/01/2005. then wants to sort like 20/01/2005 to 19/01/2006 in one sheet and 20/01/2006 to 19/01/2007 in another sheet like this for five year.

Please check my other excel file in below link and try to solve my query.

you can see my exel file name is
U:\Documents\Excel\Excel Notes\Excel Files\Date Sorting for Claims.xls

click:

http://www.mediafire.com/?mzbaxcc3zztz514

Thanks & Regards
Dnyan

 

[konew1]

Your question is not very clear to me, but I think you have a separate sheet for each client with the claims listed, and each client has a different renewal date, and you want the claims listed in date sequence with some sort of break or total at the end of each renewal year. Is that correct? I don't think it can be done with filter, sorting or pivot tables, but should be straightforward for vba code. (I don't write code, but someone else can help) Your examples above don't show how we can identify the renewal date - is it on the sheet somewhere?

 

[Dnyan]

Your question is not very clear to me, but I think you have a separate sheet for each client with the claims listed, and each client has a different renewal date, and you want the claims listed in date sequence with some sort of break or total at the end of each renewal year. Is that correct? I don't think it can be done with filter, sorting or pivot tables, but should be straightforward for vba code. (I don't write code, but someone else can help) Your examples above don't show how we can identify the renewal date - is it on the sheet somewhere?

Hi,

Thanks for reply Renewal date is not mentioned in sheet but you can take any renewal date like 20/01/2005. then claims wants to sort like 20/01/2005 to 19/01/2006 in one sheet and 20/01/2006 to 19/01/2007. Also Renewal date is change every time so is it possible?

Regards
Dnyan