Help to write formula to extract Sum Pairs

tomnyc · Mar 17, 2013

Hello,

I have a sheet that I need to find all matching pairs that net down to zero based on 2 quantity criteria. I attached a sample sheet below (Column A = Account Key, Column B = Rec Del, Column C = Calculated USD, and Column D = contractual USD).

I've made a unique key by concatenating 2 different columns, and want to 1) remove all Account keys that contain only "Del" or only "Rec" in column B, 2) from the remaining set, find all keys that contain both "Del" and "Rec" in column B such that they Net down to Zero based on Column C value, and Net down to Zero based on Column D value. Also, Column C and Column D should equal and net zero if using a negative value for "Del".

The amounts are in gross value, so you can assign a negative value for say all "Del" items in Column B. I'm trying to work the sumIf function but not sure I'm capturing everything. Can anyone help? Would be greatly appreciated. It shouldn't be too difficult for an experienced exceler. I don't think a macro is needed for this (if it is, I would need to give you all the column labels). My Entire sheet has 10 columns, with over 10,000 rows of data. I would appreciate any help you can provide. I have to create this report for tomorrow :/

[TABLE="width: 599"]
<TBODY>[TR]
[TD]AccountKey
[/TD]
[TD]Rec Del
[/TD]
[TD]Calculated USD 
[/TD]
[TD]Contractual Usd 
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001055102OPERATIONS CONTROL ACCOUNTS
[/TD]
[TD]Del
[/TD]
[TD]54,754.81 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]001092915OPERATIONS CONTROL ACCOUNTS
[/TD]
[TD]Del
[/TD]
[TD]- 
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001317205NORTHERN TRUST
[/TD]
[TD]Del
[/TD]
[TD]23,577.52 
[/TD]
[TD]23,577.00 
[/TD]
[/TR]
[TR]
[TD]00143M380DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]80.72 
[/TD]
[TD]80.00 
[/TD]
[/TR]
[TR]
[TD]00176LDN8CORPORATE CLEARANCE UNMATCHED
[/TD]
[TD]Rec
[/TD]
[TD]- 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]00176LDN8NATIONAL FINANCIAL SVCS CORP
[/TD]
[TD]Del
[/TD]
[TD]13,365.00 
[/TD]
[TD]13,365.00 
[/TD]
[/TR]
[TR]
[TD]001940915OPERATIONS CONTROL ACCOUNTS
[/TD]
[TD]Del
[/TD]
[TD]- 
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0020A2UX8UNALLOCATED
[/TD]
[TD]Del
[/TD]
[TD]1,247,171.88 
[/TD]
[TD]1,247,171.00 
[/TD]
[/TR]
[TR]
[TD]002920AC0WELLS CAPITAL MGMT
[/TD]
[TD]Del
[/TD]
[TD]- 
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003725926OPERATIONS CONTROL ACCOUNTS
[/TD]
[TD]Del
[/TD]
[TD]- 
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003725926CLEARBRIDGE ADVISORS, LLC
[/TD]
[TD]Rec
[/TD]
[TD]201,917.00 
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00386SAK8INTERCOMPANY ACCOUNTS
[/TD]
[TD]Del
[/TD]
[TD]201,916.67 
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004239109OPERATIONS CONTROL ACCOUNTS
[/TD]
[TD]Del
[/TD]
[TD]37,905.04 
[/TD]
[TD]37,905.04 
[/TD]
[/TR]
[TR]
[TD]004341AV5DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]6.75 
[/TD]
[TD]6.00 
[/TD]
[/TR]
[TR]
[TD]004341AY9DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]0.01 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]004994919OFFICE TO OFFICE HEDGE REPOS
[/TD]
[TD]Del
[/TD]
[TD]9,888.02 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]004994919OFFICE TO OFFICE HEDGE REPOS
[/TD]
[TD]Rec
[/TD]
[TD]9,888.02 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]00507K103LEGAL & GENERAL INV MGMT LTD
[/TD]
[TD]Rec
[/TD]
[TD]62,001.57 
[/TD]
[TD]62,001.00 
[/TD]
[/TR]
[TR]
[TD]00512R200DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]1.00 
[/TD]
[TD]1.00 
[/TD]
[/TR]
[TR]
[TD]00547Q201DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]13.12 
[/TD]
[TD]13.00 
[/TD]
[/TR]
[TR]
[TD]00547Q201DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Rec
[/TD]
[TD]3.71 
[/TD]
[TD]3.00 
[/TD]
[/TR]
[TR]
[TD]00547Q201NOT AVAILABLE
[/TD]
[TD]Del
[/TD]
[TD]3.71 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]005614DS8DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]30,000.00 
[/TD]
[TD]30,000.00 
[/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]138.20 
[/TD]
[TD]137.00 
[/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]0.72 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Rec
[/TD]
[TD]45.00 
[/TD]
[TD]45.00 
[/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Rec
[/TD]
[TD]2.22 
[/TD]
[TD]1.00 
[/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Rec
[/TD]
[TD]91.68 
[/TD]
[TD]91.00 
[/TD]
[/TR]
[TR]
[TD]00652G105NOT AVAILABLE
[/TD]
[TD]Del
[/TD]
[TD]150.00 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]00652G105NOT AVAILABLE
[/TD]
[TD]Rec
[/TD]
[TD]208.20 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]00687U103NOT AVAILABLE
[/TD]
[TD]Del
[/TD]
[TD]1,066.00 
[/TD]
[TD]- 
[/TD]
[/TR]
[TR]
[TD]006ESC9F6DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]97.50 
[/TD]
[TD]97.00 
[/TD]
[/TR]
[TR]
[TD]00709P108BTG PACTUAL US CAPITAL LLC
[/TD]
[TD]Del
[/TD]
[TD]379,975.00 
[/TD]
[TD]379,975.00 
[/TD]
[/TR]
[TR]
[TD]00752K105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Del
[/TD]
[TD]152,722.92 
[/TD]
[TD]152,722.00 
[/TD]
[/TR]
[TR]
[TD]00752K105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Rec
[/TD]
[TD]97,166.29 
[/TD]
[TD]208,069.00 
[/TD]
[/TR]
[TR]
[TD]00752K105DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Rec
[/TD]
[TD]44,544.64 
[/TD]
[TD]44,544.00 
[/TD]
[/TR]
[TR]
[TD]00754V109DEFAULT FOR CPI IIG/IFG ACCTS
[/TD]
[TD]Rec
[/TD]
[TD]2,000.00 
[/TD]
[TD]2,000.00 
[/TD]
[/TR]
</TBODY>[/TABLE]

Gerald Higgins · Mar 17, 2013

Hi, welcome to the board.

Just to be absolutely clear, can you explain please, for your posted sample data, exactly which rows can be netted off to zero ?

tomnyc · Mar 17, 2013

Hi,

Column C & D, Calculated USD( column C)should net down to zero if you take a negative value for "Del". Contractual USD(column D) should equal Column C and this should net down too. If they don't match, that's ok. Just need to know which don't.
Hope this helps

----------------------

tomnyc said:
Hello,

I have a sheet that I need to find all matching pairs that net down to zero based on 2 quantity criteria. I attached a sample sheet below (Column A = Account Key, Column B = Rec Del, Column C = Calculated USD, and Column D = contractual USD).

I've made a unique key by concatenating 2 different columns, and want to 1) remove all Account keys that contain only "Del" or only "Rec" in column B, 2) from the remaining set, find all keys that contain both "Del" and "Rec" in column B such that they Net down to Zero based on Column C value, and Net down to Zero based on Column D value. Also, Column C and Column D should equal and net zero if using a negative value for "Del".

The amounts are in gross value, so you can assign a negative value for say all "Del" items in Column B. I'm trying to work the sumIf function but not sure I'm capturing everything. Can anyone help? Would be greatly appreciated. It shouldn't be too difficult for an experienced exceler. I don't think a macro is needed for this (if it is, I would need to give you all the column labels). My Entire sheet has 10 columns, with over 10,000 rows of data. I would appreciate any help you can provide. I have to create this report for tomorrow :/

[TABLE="width: 599"]
<tbody>[TR]
[TD]AccountKey[/TD]
[TD]Rec Del[/TD]
[TD]Calculated USD [/TD]
[TD]Contractual Usd [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001055102OPERATIONS CONTROL ACCOUNTS[/TD]
[TD]Del[/TD]
[TD]54,754.81 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]001092915OPERATIONS CONTROL ACCOUNTS[/TD]
[TD]Del[/TD]
[TD]- [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001317205NORTHERN TRUST[/TD]
[TD]Del[/TD]
[TD]23,577.52 [/TD]
[TD]23,577.00 [/TD]
[/TR]
[TR]
[TD]00143M380DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]80.72 [/TD]
[TD]80.00 [/TD]
[/TR]
[TR]
[TD]00176LDN8CORPORATE CLEARANCE UNMATCHED[/TD]
[TD]Rec[/TD]
[TD]- [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]00176LDN8NATIONAL FINANCIAL SVCS CORP[/TD]
[TD]Del[/TD]
[TD]13,365.00 [/TD]
[TD]13,365.00 [/TD]
[/TR]
[TR]
[TD]001940915OPERATIONS CONTROL ACCOUNTS[/TD]
[TD]Del[/TD]
[TD]- [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0020A2UX8UNALLOCATED[/TD]
[TD]Del[/TD]
[TD]1,247,171.88 [/TD]
[TD]1,247,171.00 [/TD]
[/TR]
[TR]
[TD]002920AC0WELLS CAPITAL MGMT[/TD]
[TD]Del[/TD]
[TD]- [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003725926OPERATIONS CONTROL ACCOUNTS[/TD]
[TD]Del[/TD]
[TD]- [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003725926CLEARBRIDGE ADVISORS, LLC[/TD]
[TD]Rec[/TD]
[TD]201,917.00 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00386SAK8INTERCOMPANY ACCOUNTS[/TD]
[TD]Del[/TD]
[TD]201,916.67 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004239109OPERATIONS CONTROL ACCOUNTS[/TD]
[TD]Del[/TD]
[TD]37,905.04 [/TD]
[TD]37,905.04 [/TD]
[/TR]
[TR]
[TD]004341AV5DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]6.75 [/TD]
[TD]6.00 [/TD]
[/TR]
[TR]
[TD]004341AY9DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]0.01 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]004994919OFFICE TO OFFICE HEDGE REPOS[/TD]
[TD]Del[/TD]
[TD]9,888.02 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]004994919OFFICE TO OFFICE HEDGE REPOS[/TD]
[TD]Rec[/TD]
[TD]9,888.02 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]00507K103LEGAL & GENERAL INV MGMT LTD[/TD]
[TD]Rec[/TD]
[TD]62,001.57 [/TD]
[TD]62,001.00 [/TD]
[/TR]
[TR]
[TD]00512R200DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]1.00 [/TD]
[TD]1.00 [/TD]
[/TR]
[TR]
[TD]00547Q201DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]13.12 [/TD]
[TD]13.00 [/TD]
[/TR]
[TR]
[TD]00547Q201DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Rec[/TD]
[TD]3.71 [/TD]
[TD]3.00 [/TD]
[/TR]
[TR]
[TD]00547Q201NOT AVAILABLE[/TD]
[TD]Del[/TD]
[TD]3.71 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]005614DS8DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]30,000.00 [/TD]
[TD]30,000.00 [/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]138.20 [/TD]
[TD]137.00 [/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]0.72 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Rec[/TD]
[TD]45.00 [/TD]
[TD]45.00 [/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Rec[/TD]
[TD]2.22 [/TD]
[TD]1.00 [/TD]
[/TR]
[TR]
[TD]00652G105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Rec[/TD]
[TD]91.68 [/TD]
[TD]91.00 [/TD]
[/TR]
[TR]
[TD]00652G105NOT AVAILABLE[/TD]
[TD]Del[/TD]
[TD]150.00 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]00652G105NOT AVAILABLE[/TD]
[TD]Rec[/TD]
[TD]208.20 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]00687U103NOT AVAILABLE[/TD]
[TD]Del[/TD]
[TD]1,066.00 [/TD]
[TD]- [/TD]
[/TR]
[TR]
[TD]006ESC9F6DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]97.50 [/TD]
[TD]97.00 [/TD]
[/TR]
[TR]
[TD]00709P108BTG PACTUAL US CAPITAL LLC[/TD]
[TD]Del[/TD]
[TD]379,975.00 [/TD]
[TD]379,975.00 [/TD]
[/TR]
[TR]
[TD]00752K105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Del[/TD]
[TD]152,722.92 [/TD]
[TD]152,722.00 [/TD]
[/TR]
[TR]
[TD]00752K105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Rec[/TD]
[TD]97,166.29 [/TD]
[TD]208,069.00 [/TD]
[/TR]
[TR]
[TD]00752K105DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Rec[/TD]
[TD]44,544.64 [/TD]
[TD]44,544.00 [/TD]
[/TR]
[TR]
[TD]00754V109DEFAULT FOR CPI IIG/IFG ACCTS[/TD]
[TD]Rec[/TD]
[TD]2,000.00 [/TD]
[TD]2,000.00 [/TD]
[/TR]
</tbody>[/TABLE]

Gerald Higgins · Mar 18, 2013

Same question as in post #2.

From your sample data, which ROWS can be netted off ?
Please point out the specific examples which you think should be netted off against each other.

tomnyc · Mar 18, 2013

Look at the amount 9,888.02. That would be a net off. If you took a negative value for the "Del" the amounts can net to zero. That's a specific example

Gerald Higgins said:
Same question as in post #2.

From your sample data, which ROWS can be netted off ?
Please point out the specific examples which you think should be netted off against each other.

Help to write formula to extract Sum Pairs

tomnyc

New Member

Gerald Higgins

Well-known Member

tomnyc

New Member

Gerald Higgins

Well-known Member

tomnyc

New Member

Share this page

Help to write formula to extract Sum Pairs

tomnyc

New Member

Gerald Higgins

Well-known Member

tomnyc

New Member

Gerald Higgins

Well-known Member

tomnyc

New Member

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