Complex Payment schedule

[Christopherdj]

My formula query is:

I have two different payment streams. One from 01 January to 31 December and the other from 01 July to 31 December.
Any client (Column A) that has a START DATE (Column D) prior to 30 June is paid at one rate and if any client has a START DATE from (on or after) 01 July it is a different rate – that part is reasonably simple for any new clients. However –a client may have a START DATE previously in a different UNIT (column I) but the same SESSION CODE (column H), then the rate needs to default to the earliest date displayed in Column D. I’ve tried to put some workable logic to help anyone to understand the mathematical formula.

If both Client ID and Session are the same, (even though the UNIT may be different) then check the start date of the first occurrence, and if pre June 30, the outcome could be “PRE”, or if post July 01, then the outcome could be “POST”.

Scenario:
First check the client ID if it appears anywhere else, if not then use the start date on this line and populate as “PRE” or “POST”
If the client id is found, then check for the Session Code, and if not a match then use the start date on this line and populate as “PRE” or “POST”, I can then implement a lookup table to determine payments based on the outcome.
If the client id is found, and the Session Code is found, then use the earliest start date and populate as “PRE” or “POST”.

If the client id is found multiple times, then check for the Session Code, and if a match use the earliest start date and populate as “PRE” or “POST”.

A good test Client ID is 122229640 (use filter on Column A), I’ve highlighted the dates.
This client should be paid at the “PRE” rate for all, including those listed in June, September, August and October as the first date of occurrence in both sessions is prior to 30 June.
This is a rather complex formula, and it can be placed in Row “AB” of the PAYMENTS Sheet. I have hidden some of the other criteria I use to determine a very complex payment system that has just had another curly component dropped into the mix.

I do hope someone can assist.
Thanking you in advance.
Chris

[rylo]

Hi

Try this - I think I've got a handle on things.

AE2: 0
AF2: PRE
AE3: 1/7/2012
AF3: POST
AB2: =IF(COUNTIF($A$2:A2,A2)=1,VLOOKUP(D2,$AE$2:$AF$3,2),VLOOKUP(A2,$A1:AB$2,28,FALSE))

Copy AB2 down as required.

HTH

rylo

[Christopherdj]

Thanks Rylo - You are so good with quick turn arounds.

The solution you provided is ok however there is an extra criteria:

Matching the Session in Column H. The ID and date matches work fine, but the Session needs to match also.

A Newer version of file has been uploaded.

I've added an extra client ID in the top 4 lines with your formula also added to help explain - the error is in line 5 where the Session Changes.
Line 5 at "AB" should be POST as it is a new Session with a "POST" date, even though the Client ID matches. If the date in the new session was before 01/07/2012 then yes, the outcome would be "PRE".

See my original comment under Scenario above for more information.

I'm hoping it isn't too difficult.

thanks again

[rylo]

Hi

OK try this array entered in AB2 of the updated example file.

Please Login or Register  to view this content.

rylo

[Christopherdj]

I'm getting a circular reference at AB3, not sure how to fix the problem. I suspect it is a $ reference that either should or shouldn't be there, but I can't seem to fix, But it looks better.

[rylo]

Hi

My bad. Leave AB2 alone.

AB3 (array entered):

Please Login or Register  to view this content.

rylo

[Christopherdj]

Hi Rylo - thanks for your efforts so far, but I think this is more complex than what I thought and may test you.

Seems on the whole it works fine, but when checking ID 12229640 (which I highlighted dates in Orange) there is an error in the outcome.
Line 1938 start date is 01 June and should produce a 'PRE' outcome. I think the error is because the entry is well after the first session occurance which is on line 1900 with the start date of 4 October.

Because the session on line 1938 commenced on 1 June any other duplicate entry should default to the earliest date, not the first occurance on the sheet. I could sort the sheet by start date which may solve the issue, but can/would it be correct every time?

I'd appreciate your feedback on which way to approach this.
Regards
Chris

[rylo]

Hi

OK, another go.

Please Login or Register  to view this content.

rylo

[Christopherdj]

It works, thanks. My list goes forever, so I've umended the 2004 to 10000.
Thanks Rylo - again.