thehungarian
New Member
- Joined
- Apr 9, 2009
- Messages
- 3
Hi all, first post.
I have recorded a macro to do an equity and bond stock reconciliation using VLOOKUPs on market sedols, but I am having a problem making this work for alphanumeric sedols, which I think is to do with the cell format.
For example, if I was reconciling an account with three numeric sedols, I would perform the following function. Column C is the sedol and column D the number of shares.
Column C Column D
2854316 500000
6974316 250000
4934316 100000
Data Sort - Column C (Ascending).
Then on a separate worksheet I can do vlookup(cell, ColumnC:ColumnD, return value in second column)
However, if I am working on alphanumeric sedols, I have to do the following workaround in order for the vlookup to recognise them:
1 Column C Column D
2 B2583K1 500000
3 B13K98S 250000
4 B964SD3 100000
Data Sort - Column C - Ascending
Then I have to insert a formula in Range (E2:E4)
=TEXT(C2,7)
And in Range (F2:F4)
=LEFT(E2,7)
I then paste - values column F into column C.
My problem is that I have a mix of numeric and alphanumeric sedols, and this changes every month. If I apply the 'LEFT' and 'TEXT' functions to a numeric sedol, it returns a value of '7', which is no use to the lookup.
Can anyone suggest the easiest way I can use VBA to create an ascending column C in which the numeric sedols are as is, and the alphanumerics are treated as TEXT and LEFT, in which excel is able to distinguish between them?
Thanks in advance. I'm using excel 2003.
William
I have recorded a macro to do an equity and bond stock reconciliation using VLOOKUPs on market sedols, but I am having a problem making this work for alphanumeric sedols, which I think is to do with the cell format.
For example, if I was reconciling an account with three numeric sedols, I would perform the following function. Column C is the sedol and column D the number of shares.
Column C Column D
2854316 500000
6974316 250000
4934316 100000
Data Sort - Column C (Ascending).
Then on a separate worksheet I can do vlookup(cell, ColumnC:ColumnD, return value in second column)
However, if I am working on alphanumeric sedols, I have to do the following workaround in order for the vlookup to recognise them:
1 Column C Column D
2 B2583K1 500000
3 B13K98S 250000
4 B964SD3 100000
Data Sort - Column C - Ascending
Then I have to insert a formula in Range (E2:E4)
=TEXT(C2,7)
And in Range (F2:F4)
=LEFT(E2,7)
I then paste - values column F into column C.
My problem is that I have a mix of numeric and alphanumeric sedols, and this changes every month. If I apply the 'LEFT' and 'TEXT' functions to a numeric sedol, it returns a value of '7', which is no use to the lookup.
Can anyone suggest the easiest way I can use VBA to create an ascending column C in which the numeric sedols are as is, and the alphanumerics are treated as TEXT and LEFT, in which excel is able to distinguish between them?
Thanks in advance. I'm using excel 2003.
William