Hi all,
I thought I had this sorted from some previous help (here) but i am struggling again .
I have 2 worksheets with similar data as below
Sample A
Sample B
The problem is that the data on both sheets come from different sources and the only unique identifiers are ClientName and ISIN, and not necessarily in order. I'm trying to put together a formula that compares the holding amounts from both sheets.
As a clarification, Sample A, Client A holds 159,199 of Stock 1, but on Sample B the same client holds 160,000 of Stock 1. I want the formula to return error, or similar if this happens.
Any help appreciated! Thanks
I thought I had this sorted from some previous help (here) but i am struggling again .
I have 2 worksheets with similar data as below
Sample A
Book4.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | SampleA | ||||||
2 | ClientName | ISIN | Security | HoldingAmount | BookCost | ||
3 | ClientD | BMG0630Q1033 | Stock3 | 206.2600 | 22,100.00 | ||
4 | ClientB | BMG0630Q1033 | Stock3 | 120.0000 | 13,100.00 | ||
5 | ClientA | BMG0630Q1033 | Stock3 | 1,199.2500 | 127,200.00 | ||
6 | ClientC | FI0009004204 | Stock5 | 7,847,620.0000 | 0.00 | ||
7 | ClientD | FI0009004204 | Stock5 | 1,190,476.0000 | 171,221.15 | ||
8 | ClientD | FI0009620777 | Stock6 | 250,000.0000 | 0.00 | ||
9 | ClientA | GB0006061740 | Stock4 | 111,155.3260 | 160,100.00 | ||
10 | ClientB | GB0006061740 | Stock4 | 12,444.3120 | 18,100.00 | ||
11 | ClientD | GB0033843144 | Stock1 | 32,537.0000 | 36,814.75 | ||
12 | ClientB | GB0033843144 | Stock1 | 24,352.2100 | 27,699.98 | ||
13 | ClientA | GB0033843144 | Stock1 | 159,199.0000 | 183,100.00 | ||
14 | ClientD | U002133 | Stock2 | 58,000.0000 | 58,100.00 | ||
Sample A |
Sample B
Book4.xls | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | SampleB | ||||||
2 | ClientName | ISIN | Security | HoldingAmount | BookCost | ||
3 | ClientA | GB0033843144 | Stock1 | 160,000.0000 | 180,000.00 | ||
4 | ClientA | U002133 | Stock2 | 296,000.0000 | 296,100.00 | ||
5 | ClientA | BMG0630Q1033 | Stock3 | 1,199.2500 | 127,200.00 | ||
6 | ClientA | GB0006061740 | Stock4 | 111,155.3260 | 160,100.00 | ||
7 | ClientB | GB0033843144 | Stock1 | 25,000.0000 | 27,699.98 | ||
8 | ClientB | FI0009004204 | Stock5 | 12,444.3120 | 17,100.00 | ||
9 | ClientC | FI0009004204 | Stock5 | 7,847,620.0000 | 0.00 | ||
10 | ClientD | U002133 | Stock2 | 58,000.0000 | 58,100.00 | ||
11 | ClientD | BMG0630Q1033 | Stock3 | 206.2600 | 22,100.00 | ||
12 | ClientD | GB0033843144 | Stock1 | 32,537.0000 | 36,814.75 | ||
13 | ClientD | FI0009004204 | Stock5 | 1,190,476.0000 | 171,221.15 | ||
14 | ClientD | FI0009620777 | Stock6 | 250,000.0000 | 0.00 | ||
Sample B |
The problem is that the data on both sheets come from different sources and the only unique identifiers are ClientName and ISIN, and not necessarily in order. I'm trying to put together a formula that compares the holding amounts from both sheets.
As a clarification, Sample A, Client A holds 159,199 of Stock 1, but on Sample B the same client holds 160,000 of Stock 1. I want the formula to return error, or similar if this happens.
Any help appreciated! Thanks