Evening all,
Any assitance greatly appreciated
I have 2 worksheets (List One and List Two) as below. I want to be able to compare the List One holding amount for each client, for each stock with the corresponding List Two data. If the 2 match, then OK is returned next to the row in question, if not then something like Error.
As a further explation (its late!), on List One
Client A holds 159,199 units in Stock 1, but
on List Two
Client A holds 160,000 units in Stock 1
which is wrong. I've been trying to work out some formula to this, but with no joy. The closest I've come is the Look up one value and return one corresponding value
I guess I want look up 2 values and return one coresponding value.
Forgot to say the only 2 unique fields in each list are ClientName and ISIN....
Thanks in advance
Any assitance greatly appreciated
I have 2 worksheets (List One and List Two) as below. I want to be able to compare the List One holding amount for each client, for each stock with the corresponding List Two data. If the 2 match, then OK is returned next to the row in question, if not then something like Error.
As a further explation (its late!), on List One
Client A holds 159,199 units in Stock 1, but
on List Two
Client A holds 160,000 units in Stock 1
which is wrong. I've been trying to work out some formula to this, but with no joy. The closest I've come is the Look up one value and return one corresponding value
I guess I want look up 2 values and return one coresponding value.
Book4.xls | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | ListOne | ListTwo | |||||||||||
2 | ClientName | ISIN | Security | HoldingAmount | BookCost | ClientName | ISIN | Security | HoldingAmount | BookCost | |||
3 | ClientA | GB0033843144 | Stock1 | 159,199.0000 | 183,100.00 | ClientA | GB0033843144 | Stock1 | 160,000.0000 | 180,000.00 | |||
4 | ClientA | U002133 | Stock2 | 296,000.0000 | 296,100.00 | ClientA | U002133 | Stock2 | 296,000.0000 | 296,100.00 | |||
5 | ClientA | BMG0630Q1033 | Stock3 | 1,199.2500 | 127,200.00 | ClientA | BMG0630Q1033 | Stock3 | 1,199.2500 | 127,200.00 | |||
6 | ClientA | GB0006061740 | Stock4 | 111,155.3260 | 160,100.00 | ClientA | GB0006061740 | Stock4 | 111,155.3260 | 160,100.00 | |||
7 | ClientB | GB0033843144 | Stock1 | 24,352.2100 | 27,699.98 | ClientB | GB0033843144 | Stock1 | 25,000.0000 | 27,699.98 | |||
8 | ClientB | BMG0630Q1033 | Stock3 | 120.0000 | 13,100.00 | ClientB | BMG0630Q1033 | Stock3 | 120.0000 | 13,100.00 | |||
9 | ClientB | GB0006061740 | Stock4 | 12,444.3120 | 18,100.00 | ClientB | GB0006061856 | Stock7 | 12,444.3120 | 17,100.00 | |||
10 | ClientC | FI0009004204 | Stock5 | 7,847,620.0000 | 0.00 | ClientC | FI0009004204 | Stock5 | 7,847,620.0000 | 0.00 | |||
11 | ClientD | GB0033843144 | Stock1 | 32,537.0000 | 36,814.75 | ClientD | GB0033843144 | Stock1 | 32,537.0000 | 36,814.75 | |||
12 | ClientD | U002133 | Stock2 | 58,000.0000 | 58,100.00 | ClientD | U002133 | Stock2 | 58,000.0000 | 58,100.00 | |||
13 | ClientD | BMG0630Q1033 | Stock3 | 206.2600 | 22,100.00 | ClientD | BMG0630Q1033 | Stock3 | 206.2600 | 22,100.00 | |||
14 | ClientD | FI0009004204 | Stock5 | 1,190,476.0000 | 171,221.15 | ClientD | FI0009004204 | Stock5 | 1,190,476.0000 | 171,221.15 | |||
15 | ClientD | FI0009620777 | Stock6 | 300,000.0000 | 0.00 | ClientD | FI0009620777 | Stock6 | 250,000.0000 | 0.00 | |||
16 | ClientE | FI0009004204 | Stock5 | 19,356,766.0000 | 134,601.54 | ClientE | FI0009004204 | Stock5 | 19,356,766.0000 | 134,601.54 | |||
One |
Forgot to say the only 2 unique fields in each list are ClientName and ISIN....
Thanks in advance