Excel Claim Sorting

**Dnyan** · 07-22-2011, 08:26 AM

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.

I attached my excel file but it was sorting only month wise but I want claims sorting yearly.

**Azam Ali** · 07-22-2011, 09:21 AM

Hi Dnyan

find the attatched file

**Dnyan** · 07-22-2011, 10:39 AM

Originally Posted by Azam Ali

Hi Dnyan

find the attatched file

Hi,

Many Many Thanks, its working properly.

and How can I increase the range up to 100 rows.

I have another query that is after sorting this claims,

is it possible to calculate year wise summery like:

1. Count no of claims paid & total amount
2. Count no of claims outstanding & Total amount

please check attached File Summery sheet.

Thanks & Regard
Dnyan

**Azam Ali** · 07-22-2011, 11:02 AM

find the attatched file

**Dnyan** · 07-22-2011, 11:14 AM

Originally Posted by Azam Ali

find the attatched file

Hi,

Excellent

Many Many Thanks for your help............

Regards
Dnyan

**Azam Ali** · 07-23-2011, 02:10 AM

Welcome

If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

**Dnyan** · 07-25-2011, 08:36 AM

Originally Posted by Azam Ali

Welcome

If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

Hi,

Thanks for guide me.
I have one more query that there is error is coming in Claims Sorted sheet.

I made some changes in date, because I want all date are automated.
If I type date in Data Sheet then all the calculation will be automatically process by one date.

Error in Vlookup formula is not given correct answer &
Claims are not sorted out.

please check

Regards
Dnyan

**Azam Ali** · 07-26-2011, 01:14 AM

Find the attatched file

**Dnyan** · 07-26-2011, 02:42 AM

Originally Posted by Azam Ali

Find the attatched file

Hi,

Heartly Thanks for help me........

Regards
Dnyan

**Dnyan** · 07-26-2011, 06:46 AM

Originally Posted by Azam Ali

Find the attatched file

Hi,

Sorry but I have one more query now I am sorting summery by individual Type.
I made some changes in formula but I do not gate correct answer.

Please check in sheet "EL" in redmark cells I edit the formula.
I want count and sum of individual Type of claim.

I use this formula but not given correct answer.

=SUMPRODUCT(--(Data!$F$3:$F$1002="EL"),--(Data!$B$3:$B$1002>=S3),--(Data!$B$3:$B$1002<=U3),--(Data!$C$3:$C$1002>0))

Please help me about the formula for summery.

Regards
Dnyan

**Azam Ali** · 07-26-2011, 07:01 AM

Hi Dnyan

the formula in red highlighted cell is a count formula and giving correct results.

If you need the sum add ,Data!$C$3:$C$1002 before last bracket

**Dnyan** · 07-26-2011, 07:26 AM

Hi,

Thanks Now the formula is working correctly.

Thanks Again

After complete of this tracker I will put this thread in Solved.

Regards
Dnyan

**Dnyan** · 07-26-2011, 08:47 AM

Hi,

Now there are two more things required

1. if Paid amount and outstanding amount is given then no of claim will be count only paid, not outstanding.
2. Zero amount claim should be count.

Zero amount claims are not considered in summery sheet.
Please check highlighted area in Data sheet and EL summery check there is twice count of paid and outstanding and no count of zero claims.

Please check if there is any option to do this.

Regards
Dnyan

**Azam Ali** · 07-27-2011, 03:25 AM

find the attatched file

**Dnyan** · 07-27-2011, 03:26 AM

Hi,

I try to put conditions but unable to find correct answer.

So please help about this.

Dnyan

**Azam Ali** · 07-27-2011, 03:59 AM

Hi Dnyan

the first table is linked with Cell A3, change the name in A3 and you will get your answer. otherwise mentin the name in every table

**Dnyan** · 07-27-2011, 07:40 AM

Hi,

I put the formula in all place but one more error is coming that if Only outstanding amount is given then count showing in paid and outstanding coloumn.

Now I change the formula in paid coloumn and get correct answer. but again ther problem is coming that if paid and outstanding amount is given then number of paid claim should be count that is not counting any number.

If paid claim given then count will be only paid
if paid claim & outstandng cliam given then count will be only paid claim
if only outstanding claim given then count will be only outstanding claim
if zero amount given then count will be paid claim count

please check attach file

Dnyan

**Azam Ali** · 07-27-2011, 08:48 AM

from the Cell # H3 in summary sheet remove ,--(ISNUMBER(Data!$D$5:$D$1004)=FALSE) from formula and copy down

date in Cell # B6 of data sheet is wrong. I find no other error

**Dnyan** · 07-27-2011, 11:34 AM

Hi,

I change the formula but still the error is coming if outstanding amount is given then count showing in paid and outstanding coloumn.

You can check below conditions and put the date in data sheet then you will know the error.

If paid claim given then count will be only paid = correct

I make some mistake to tell you before now the conditions is - if paid claim & outstandng cliam given then count will be only Outstanding claim. = ?

if only outstanding claim given then count will be only outstanding claim = wrong

if zero amount given in paid then count will be paid claim count and if zero amount given in outstanding claim then count will be outstanding claim only.= ?

please check data sheet and summery sheet in attach file.

Dnyan

**Dnyan** · 07-28-2011, 05:24 AM

Hi,

Still Confusion............

Please help

**Dnyan** · 07-29-2011, 10:04 AM

Hi,

Please help.....

Query still pending.....

Regards
Dnyan

**Azam Ali** · 07-31-2011, 11:43 PM

In cell H3 use
=SUMPRODUCT(--(Data!$F$5:$F$1004=$A$3),--(Data!$B$5:$B$1004>=R3),--(Data!$B$5:$B$1004<=T3),--(ISNUMBER(Data!$D$5:$D$1004)=FALSE)) and copy down

in cell J3 use
=SUMPRODUCT(--(Data!$F$5:$F$1004=$A$3),--(Data!$B$5:$B$1004>=R3),--(Data!$B$5:$B$1004<=T3),--ISNUMBER(Data!$D$5:$D$1004)) and copy down

**Dnyan** · 08-01-2011, 01:00 AM

Hi,

Thank you very much.

Actually I tried already it was not work but not its working perfect.

Thanks again

Regards
Dnyan

**Azam Ali** · 08-01-2011, 02:10 AM

welcome

If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

Excel Claim Sorting

LinkBack

Thread Tools

Rate This Thread

Display

Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Re: Excel Claim Sorting

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions