Please help! Positive/Negative Account offset???

Anteka

New Member
Joined
Aug 17, 2008
Messages
5
Hi There,

I have a spreadsheet containing approx 36000 lines of information.

Column headings are: Business Unit, Cost Centre, Account Number, Amount etc.

I need to be able to identify the positive and negative Amounts on the same Account within the same Business Unit.

Ideally delete these Matching items or at least allow me to then manually filter and delete these lines of information.

Only leaving the outstanding items.

As this Spreadsheet goes back quite a few years. The same account, amount, business unit, cost centre can be within the spreadsheet multiple times.

Hope someone can help me.

Thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
The formula in f2 copy down should have been:
=COUNTIFS($E$2:$E$11,E2,$D$2:$D$11,-D2)

the IF statement is obsolete.

In Excel prior to 07
=SUMPRODUCT(--($E$2:$E$11=E2),--($D$2:$D$11=-D2))
 
Last edited:
Upvote 0
That's Great! Thank you very much!!!!

I will go into work tomorrow and see if I can apply this to my spreadsheet. Will let you know how I got on.

Thanks again

:biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top