Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Business Unit</td><td style=";"> Cost Centre</td><td style=";"> Account Number</td><td style=";"> Amount </td><td style=";">Concat</td><td style=";">MATCHES</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style=";">E</td><td style="text-align: right;;">1</td><td style="text-align: right;;">-1</td><td style=";">AE1</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">A</td><td style=";">E</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style=";">AE1</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">A</td><td style=";">E</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style=";">AE3</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">E</td><td style=";">E</td><td style="text-align: right;;">2</td><td style="text-align: right;;">-1</td><td style=";">EE2</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">E</td><td style=";">B</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style=";">EB2</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">E</td><td style=";">B</td><td style="text-align: right;;">1</td><td style="text-align: right;;">-1</td><td style=";">EB1</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">C</td><td style=";">B</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style=";">CB1</td><td style="text-align: right;;">1</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">D</td><td style=";">B</td><td style="text-align: right;;">2</td><td style="text-align: right;;">-1</td><td style=";">DB2</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">D</td><td style=";">B</td><td style="text-align: right;;">2</td><td style="text-align: right;;">-2</td><td style=";">DB2</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">C</td><td style=";">B</td><td style="text-align: right;;">1</td><td style="text-align: right;;">-2</td><td style=";">CB1</td><td style="text-align: right;;">1</td></tr></tbody></table>
Sheet2
<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">
Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E2</th><td style="text-align:left">=A2&B2&C2</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F2</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E2,$D$2:$D$11,IF(D2>0,-D2,-D2))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E3</th><td style="text-align:left">=A3&B3&C3</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F3</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E3,$D$2:$D$11,IF(D3>0,-D3,-D3))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E4</th><td style="text-align:left">=A4&B4&C4</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F4</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E4,$D$2:$D$11,IF(D4>0,-D4,-D4))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E5</th><td style="text-align:left">=A5&B5&C5</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F5</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E5,$D$2:$D$11,IF(D5>0,-D5,-D5))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E6</th><td style="text-align:left">=A6&B6&C6</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F6</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E6,$D$2:$D$11,IF(D6>0,-D6,-D6))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E7</th><td style="text-align:left">=A7&B7&C7</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F7</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E7,$D$2:$D$11,IF(D7>0,-D7,-D7))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E8</th><td style="text-align:left">=A8&B8&C8</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F8</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E8,$D$2:$D$11,IF(D8>0,-D8,-D8))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E9</th><td style="text-align:left">=A9&B9&C9</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F9</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E9,$D$2:$D$11,IF(D9>0,-D9,-D9))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E10</th><td style="text-align:left">=A10&B10&C10</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F10</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E10,$D$2:$D$11,IF(D10>0,-D10,-D10))</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E11</th><td style="text-align:left">=A11&B11&C11</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">F11</th><td style="text-align:left">=COUNTIFS(
$E$2:$E$11,E11,$D$2:$D$11,IF(D11>0,-D11,-D11))</td></tr></tbody></table></td></tr></tbody></table>
All 1 are giving equivalent accounts.
This could be done with 1 formula but I think this way is more readable.
I have taken into accunt the Cost Centre as well