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