Grouping data by looking up a date range & consolidating (Excel 2007)

sanation · Jan 30, 2012

I am creating templates for preparing and posting Credit Card & Petty Cash expenses for a multi-location business using Excel 2007.

The Manager enters the purchase details, including a GL code into a summary tab:

<table width="843" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:5741;width:118pt" width="157"> <col style="width:113pt" width="151"> <col style="mso-width-source:userset;mso-width-alt:9910;width:203pt" width="271"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:3218; width:66pt" span="2" width="88"> </colgroup><tbody><tr style="mso-height-source:userset;height:32.25pt" height="43"> <td class="xl69" style="height:32.25pt;width:118pt" width="157" height="43">Date</td> <td class="xl70" style="width:113pt" width="151">Expense Code</td> <td class="xl70" style="width:203pt" width="271">Description</td> <td class="xl71" style="width:66pt" width="88">Amount (Inc. GST)</td> <td class="xl71" style="width:66pt" width="88">GST</td> <td class="xl71" style="width:66pt" width="88">Amount (Exc. GST)</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl72" style="height:18.0pt" height="24">02-Jan-12</td> <td class="xl73">6179</td> <td class="xl74">Cleaning Products</td> <td class="xl75" align="right">16.85</td> <td class="xl76" align="right">1.53</td> <td class="xl75" align="right">15.32</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl72" style="height:18.0pt;border-top:none" height="24">03-Jan-12</td> <td class="xl73" style="border-top:none">6330</td> <td class="xl74" style="border-top:none">Milk</td> <td class="xl75" style="border-top:none" align="right">2.00</td> <td class="xl76" style="border-top:none" align="right">0.00</td> <td class="xl75" style="border-top:none" align="right">2.00</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl72" style="height:18.0pt;border-top:none" height="24">09-Jan-12</td> <td class="xl73" style="border-top:none">6330</td> <td class="xl74" style="border-top:none">Coffee</td> <td class="xl75" style="border-top:none" align="right">18.00</td> <td class="xl76" style="border-top:none" align="right">0.00</td> <td class="xl75" style="border-top:none" align="right">18.00</td> </tr> <tr style="mso-height-source:userset;height:18.0pt" height="24"> <td class="xl72" style="height:18.0pt;border-top:none" height="24">15-Jan-12</td> <td class="xl73" style="border-top:none">6630</td> <td class="xl74" style="border-top:none">Postage</td> <td class="xl75" style="border-top:none" align="right">5.60</td> <td class="xl76" style="border-top:none" align="right">0.51</td> <td class="xl75" style="border-top:none" align="right">5.09</td> </tr> </tbody></table>

I then want this data to feed into a Journal sheet that can be simply copied and pasted into our ERP system.

Simple in theory, but the added challenge (for me) is that I need the data to then be group into different date ranges so as to correspond with our accounting periods.

<table width="593" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:2048;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:7680;width:158pt" width="210"> <col style="mso-width-source:userset;mso-width-alt:7460;width:153pt" width="204"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:92pt" width="123" height="20">Financial Year</td> <td class="xl68" style="width:42pt" width="56">Period</td> <td class="xl67" style="width:158pt" width="210">From</td> <td class="xl67" style="width:153pt" width="204">To</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">1</td> <td class="xl71">Monday, 27 June 2011</td> <td class="xl71">Sunday, 24 July 2011</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">2</td> <td class="xl71">Monday, 25 July 2011</td> <td class="xl71">Sunday, 21 August 2011</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">3</td> <td class="xl71">Monday, 22 August 2011</td> <td class="xl71">Sunday, 18 September 2011</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">4</td> <td class="xl71">Monday, 19 September 2011</td> <td class="xl71">Sunday, 16 October 2011</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">5</td> <td class="xl71">Monday, 17 October 2011</td> <td class="xl71">Sunday, 13 November 2011</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">6</td> <td class="xl71">Monday, 14 November 2011</td> <td class="xl71">Sunday, 11 December 2011</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">7</td> <td class="xl71">Monday, 12 December 2011</td> <td class="xl71">Sunday, 8 January 2012</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">8</td> <td class="xl71">Monday, 9 January 2012</td> <td class="xl71">Sunday, 5 February 2012</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">9</td> <td class="xl71">Monday, 6 February 2012</td> <td class="xl71">Sunday, 4 March 2012</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">10</td> <td class="xl71">Monday, 5 March 2012</td> <td class="xl71">Sunday, 1 April 2012</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">11</td> <td class="xl71">Monday, 2 April 2012</td> <td class="xl71">Sunday, 29 April 2012</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">12</td> <td class="xl71">Monday, 30 April 2012</td> <td class="xl71">Sunday, 27 May 2012</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl69" style="height:14.25pt" height="19">2011/2012</td> <td class="xl70">13</td> <td class="xl71">Monday, 28 May 2012</td> <td class="xl71">Sunday, 24 June 2012</td> </tr> </tbody></table>
The accounting periods are 28 days and the Credit Card Statements, for example, are for the Calendar month.

I am need assistance with the formula, in this instance to group the transactions made after the 08-Jan-12 (in bold below) to be recorded in the Period 8 section:

<table width="676" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:94pt" span="2" width="125"> <col style="mso-width-source:userset;mso-width-alt:11008;width:226pt" width="301"> <col style="width:94pt" width="125"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt;width:94pt" width="125" height="20">Financial Year:</td> <td class="xl65" style="width:94pt" width="125">2011/2012</td> <td class="xl66" style="width:226pt" width="301">
</td> <td class="xl66" style="width:94pt" width="125">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">
</td> <td class="xl65">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">Posting Period:</td> <td class="xl65">7</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">Posting Date:</td> <td class="xl71">08-Jan-12</td> <td class="xl66">
</td> <td class="xl67">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl71" style="height:14.25pt" height="19">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl67">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl71" style="height:14.25pt" height="19">02-Jan-12</td> <td class="xl65"> 4339006179</td> <td class="xl66">Cleaning Products</td> <td class="xl67" align="right">15.32</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl71" style="height:14.25pt" height="19">03-Jan-12</td> <td class="xl65"> 4339006330</td> <td class="xl66">Milk</td> <td class="xl67" align="right">2.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">09-Jan-12</td> <td class="xl76"> 4339006330</td> <td class="xl74">Coffee</td> <td class="xl77" align="right">18.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">15-Jan-12</td> <td class="xl76"> 4339006630</td> <td class="xl74">Postage</td> <td class="xl77" align="right">5.09</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl72" align="right">40.41</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">
</td> <td class="xl65">
</td> <td class="xl66">
</td> <td class="xl67">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">
</td> <td class="xl65">
</td> <td class="xl66">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">Posting Period:</td> <td class="xl65">8
</td> <td class="xl66">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl69" style="height:15.0pt" height="20">Posting Date:</td> <td class="xl71">05-Feb-12</td> <td class="xl66">
</td> <td class="xl67">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">
</td> <td class="xl65">
</td> <td class="xl66">
</td> <td class="xl67">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl66" style="height:14.25pt" height="19">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl72" align="right">0.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl66" style="height:15.75pt" height="21">
</td> <td class="xl66">
</td> <td class="xl68">TOTAL:</td> <td class="xl75" align="right">40.41</td> </tr> </tbody></table>

I'm not sure what type of formula would be best and whether the date ranges for the periods are set up appropriately for what I'm trying to achieve.

Any assistance anyone can provide would be appreciated.

Grouping data by looking up a date range & consolidating (Excel 2007)

sanation

New Member

Similar threads

Share this page

Grouping data by looking up a date range & consolidating (Excel 2007)

sanation

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock