I need an array formula Genius, I think....

ExampleWB.xlsx
My apologies for poor description, in short i have no idea what function(s) will resolve my requirement so the best i can do is explain (and hope). Ive also done a mock up WB (attached) to illustrate further the problem at hand. Here we go...

So whenever we receive an invoice from a subcontractor we record the transaction in my 'Subcontractors'! sheet on my WB. Each transaction (invoice received) occupies its own unique row in the table.

As a contractor i have a responsibility to deduct tax from subcontractor invoices on a monthly basis (the actual time periods can be found on the ExampleWB attached) Upon doing this i generate a tax statement to supply to the subcontractor showing all deductions made during this period. Each statement has a 'statement number'. Now where it gets tricky, some subcontractors may send multiple invoices in the same period but the tax statement has to account for all of that particular subcontractors invoices in one statement. So on occasion multiple invoice transactions to the same contractor in the same period have to be manually labelled with the same tax statement number example '1001'

I have no problem on the statement side of things because i simply label a cell in my statement template with the statement number i want to generate (example 1001) and then simply use a SUMIF function to look at the 'Subcontractors'! sheet look for all transactions labelled '1001' and it returns the totals of all relevant invoices required to the relevant cells in the statement. This completes my whole statement simply by entering the Statement number. Fine no problem.

My problem is, based on the quantity of invoices we receive and because we enter them as we receive them, it can be not only time consuming but leaves potential for error when me 'manually assign' the statement numbers to each record. Typical example being, we could assign the wrong statement number because we may not check/read the date correctly (other errors of course possible when doing this) So i think i need an array formula or something similar to automate this 'labeling'

As mentioned I've attached a example WB with a breakdown to further help. This may be an impossible request, but i thought id put the challenge out there anyhow.

I thank anyone in advance who at least takes the time to take a look at this, and lots of respect to anyone that has any advice for me regarding this.

If i can help in any way please don't hesitate to ask.

Thank you.

ExampleWB.xlsx

Apologies, i noticed a typo that may have been misleading, fresh copy here

Originally Posted by XOR LX

Hi,

Can you just confirm: you only operate an 11-Period Year (no March Period)?

And that (rather illogically it would seem to me), for example, the February Period consists of approximately 90% January dates, and so on?

If so, in which Period is 10 February 2014 - which appears in your sheet - to be placed?

Regards

Thank you for your reply

Apologies i must have deleted that row somehow when copy/Pasting table from another sheet March should show 6th of Feb to the 5th Of march

Reason being on the Tax return i report (for example) "for the month ending 5th of "Given month". Example for month ending 5th of Feb would be the period between the 6th of Jan to the 5th of Feb etc

Hope this helps?

Originally Posted by alfgrey

Thank you for your reply

Apologies i must have deleted that row somehow when copy/Pasting table from another sheet March should show 6th of Feb to the 5th Of march

Reason being on the Tax return i report (for example) "for the month ending 5th of "Given month". Example for month ending 5th of Feb would be the period between the 6th of Jan to the 5th of Feb etc

Hope this helps?

The Listing of the 'Months' and their relative periods are only there for the benefit of another function elsewhere in the WB.

For the function were discussing i assume the Labelled month is irelevant and rather it is only the from and to dates that are required?

Originally Posted by XOR LX

Thanks.

I'm not sure how you arrive at your desired result for row 12:

Date: 10 February 2014
CIS Statement number: 1006
Subcontractor: John

You appear to have designated a new CIS Statement number on this line, though row 6 also has this Subcontractor and Date? Please clarify.

Regards

Very well spotted! This should of course read 1003 the same as row 6 i made a typo it would seem, This proves why i need this to be automated, i got this wrong with just a few examples. I am very sorry for this! For you to have spotted that tells me you understand my requiremnt better than i do Thank you for checking i feel terrible now....

Originally Posted by daddylonglegs

Hello alfgrey,

Assuming you enter the first value 1001 manually in B2 then try this formula in B3 copied down

=IF(A3="","",IF(COUNTIFS(C$2:C2,C3,A$2:A2,">"&EOMONTH(A3-5,-1)+5),LOOKUP(2,1/(C$2:C2=C3)/(A$2:A2>EOMONTH(A3-5,-1)+5),B$2:B2),MAX(B$2:B2)+1))

Well to say I'm jealous that you worked that out is an understatement! Thank you so much for you help! Works exactly how I imagined!! I hope i can understand excel to that level one day and return the help you have given me to others. Thanks again!!!!

Originally Posted by FDibbins

alfgrey, I am happy you got your question answered

However, for future reference, please give your threads a tilte that actually describes your problem

Ok sure, the first line of my post was apologising for the title, I had no idea which functions I needed for this requirement. Won't happen again.