SHARRIS2013
New Member
- Joined
- Jan 3, 2013
- Messages
- 10
Hi,
I have a set of data like the below in sheet 1, i need to match the data by order number and show differences as exeptions on sheet 2
<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=5></COLGROUP>
Rules:
Based on 2 matching order numbers
currency = same
date = same
purchase and sale = should be one of each amongst the two order number entries
either amt 1 or amt 2 must be the same (it wont necesirily always be amt 1)
outputs for sheet 2:
single order numbers
matching order numbers not meeting the above rules
(so with the above i would see order 567 as a single and 999 as a mismatch, due to date difference)
Is it possible to achieve the above in excel? Or would i need to use access? If someone could assist with the code that would be great!
Thanks!
I have a set of data like the below in sheet 1, i need to match the data by order number and show differences as exeptions on sheet 2
Order</SPAN> | Purchase/Sale</SPAN> | Currency</SPAN> | CCY 1</SPAN> | AMT 1</SPAN> | CCY 2</SPAN> | AMT 2</SPAN> | Date |
123</SPAN> | P</SPAN> | GBP/USD</SPAN> | GBP</SPAN> | 100</SPAN> | USD</SPAN> | 160</SPAN> | 29/01/2013 |
567</SPAN> | S</SPAN> | EUR/GBP</SPAN> | EUR</SPAN> | 125</SPAN> | GBP</SPAN> | 100</SPAN> | 29/01/2013 |
123</SPAN> | S</SPAN> | GBP/USD</SPAN> | GBP</SPAN> | 100</SPAN> | USD</SPAN> | 170</SPAN> | 29/01/2013 |
999</SPAN> | S</SPAN> | EUR/GBP</SPAN> | EUR</SPAN> | 125</SPAN> | GBP</SPAN> | 100</SPAN> | 28/01/2013 |
999</SPAN> | P</SPAN> | EUR/GBP</SPAN> | EUR</SPAN> | 115</SPAN> | GBP</SPAN> | 100</SPAN> | 29/01/2013 |
<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=5></COLGROUP>
Rules:
Based on 2 matching order numbers
currency = same
date = same
purchase and sale = should be one of each amongst the two order number entries
either amt 1 or amt 2 must be the same (it wont necesirily always be amt 1)
outputs for sheet 2:
single order numbers
matching order numbers not meeting the above rules
(so with the above i would see order 567 as a single and 999 as a mismatch, due to date difference)
Is it possible to achieve the above in excel? Or would i need to use access? If someone could assist with the code that would be great!
Thanks!