Hello, I have a small excel problem that I was wondering if anyone have a good solution for.
I have a list of transactions that are linked to a profit center and an account, these transactions could be removed if:
• There is a + and a – transaction for the same amount and the same profit center and account.
• Another case where the transactions could be removed is where there are multiple transactions with the same + & - as the two transactions, but where there are more then two. In this case the oldest transactions could be removed, and only the latest should be kept.
If there are only one transaction for the profit center and account at that amount, the transaction should be kept.
I have tried to explain the logic in the table below.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Profit Center</th><th>Account</th><th>Profit Center - Account</th><th>Document Date</th><th>Posting Date</th><th>Amount</th><th>Remove/Keep</th></tr></thead><tbody>
<tr><td>0053</td><td>611000</td><td>0053-611000</td><td>30.04.2015</td><td>15.05.2015</td><td>200</td><td>Keep</td></tr>
<tr><td>0053</td><td>611010</td><td>0053-611010</td><td>25.08.2015</td><td>25.08.2015</td><td>350</td><td>Keep</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>15.05.2015</td><td>15.05.2015</td><td>200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>15.05.2015</td><td>15.05.2015</td><td>-200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>15.05.2015</td><td>15.05.2015</td><td>200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>30.07.2015</td><td>31.07.2015</td><td>-200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611200</td><td>0053-611200</td><td>01.01.2015</td><td>01.01.2015</td><td>-1500</td><td>Remove</td></tr>
<tr><td>0053</td><td>611200</td><td>0053-611200</td><td>09.02.2015</td><td>10.02.2015</td><td>1500</td><td>Remove</td></tr>
<tr><td>0053</td><td>611200</td><td>0053-611200</td><td>11.05.2015</td><td>31.05.2015</td><td>1500</td><td>Keep</td></tr>
<tr><td>0053</td><td>611500</td><td>0053-611500</td><td>01.01.2015</td><td>03.01.2015</td><td>2000</td><td>Remove</td></tr>
<tr><td>0053</td><td>611500</td><td>0053-611500</td><td>06.04.2015</td><td>06.04.2015</td><td>-2000</td><td>Keep</td></tr>
<tr><td>0053</td><td>611500</td><td>0053-611500</td><td>09.02.2015</td><td>09.02.2015</td><td>-2000</td><td>Remove</td></tr>
<tr><td>0053</td><td>612000</td><td>0053-612000</td><td>19.06.2015</td><td>25.06.2015</td><td>300</td><td>Keep</td></tr>
<tr><td>0053</td><td>612000</td><td>0053-612000</td><td>21.09.2015</td><td>21.09.2015</td><td>500</td><td>Keep</td></tr>
<tr><td>0053</td><td>612000</td><td>0053-612000</td><td>29.06.2015</td><td>29.06.2015</td><td>200</td><td>Keep</td></tr>
</tbody></table>
I appreciate any assistance I could get on this excel problem.
I have a list of transactions that are linked to a profit center and an account, these transactions could be removed if:
• There is a + and a – transaction for the same amount and the same profit center and account.
• Another case where the transactions could be removed is where there are multiple transactions with the same + & - as the two transactions, but where there are more then two. In this case the oldest transactions could be removed, and only the latest should be kept.
If there are only one transaction for the profit center and account at that amount, the transaction should be kept.
I have tried to explain the logic in the table below.
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Profit Center</th><th>Account</th><th>Profit Center - Account</th><th>Document Date</th><th>Posting Date</th><th>Amount</th><th>Remove/Keep</th></tr></thead><tbody>
<tr><td>0053</td><td>611000</td><td>0053-611000</td><td>30.04.2015</td><td>15.05.2015</td><td>200</td><td>Keep</td></tr>
<tr><td>0053</td><td>611010</td><td>0053-611010</td><td>25.08.2015</td><td>25.08.2015</td><td>350</td><td>Keep</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>15.05.2015</td><td>15.05.2015</td><td>200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>15.05.2015</td><td>15.05.2015</td><td>-200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>15.05.2015</td><td>15.05.2015</td><td>200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611100</td><td>0053-611100</td><td>30.07.2015</td><td>31.07.2015</td><td>-200</td><td>Remove</td></tr>
<tr><td>0053</td><td>611200</td><td>0053-611200</td><td>01.01.2015</td><td>01.01.2015</td><td>-1500</td><td>Remove</td></tr>
<tr><td>0053</td><td>611200</td><td>0053-611200</td><td>09.02.2015</td><td>10.02.2015</td><td>1500</td><td>Remove</td></tr>
<tr><td>0053</td><td>611200</td><td>0053-611200</td><td>11.05.2015</td><td>31.05.2015</td><td>1500</td><td>Keep</td></tr>
<tr><td>0053</td><td>611500</td><td>0053-611500</td><td>01.01.2015</td><td>03.01.2015</td><td>2000</td><td>Remove</td></tr>
<tr><td>0053</td><td>611500</td><td>0053-611500</td><td>06.04.2015</td><td>06.04.2015</td><td>-2000</td><td>Keep</td></tr>
<tr><td>0053</td><td>611500</td><td>0053-611500</td><td>09.02.2015</td><td>09.02.2015</td><td>-2000</td><td>Remove</td></tr>
<tr><td>0053</td><td>612000</td><td>0053-612000</td><td>19.06.2015</td><td>25.06.2015</td><td>300</td><td>Keep</td></tr>
<tr><td>0053</td><td>612000</td><td>0053-612000</td><td>21.09.2015</td><td>21.09.2015</td><td>500</td><td>Keep</td></tr>
<tr><td>0053</td><td>612000</td><td>0053-612000</td><td>29.06.2015</td><td>29.06.2015</td><td>200</td><td>Keep</td></tr>
</tbody></table>
I appreciate any assistance I could get on this excel problem.
