Sedol help - formatting numeric and alphanumeric data

[thehungarian]

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

 

[lenze]

Hi and welcome to the board!!!
What is the 4th argument in your VLOOKUP formula? It should be 0 or FALSE.

=VLOOKUP(A1,C1:D100,2,FALSE)

No need to sort
lenze

 

[thehungarian]

Hi there, I am using 0

e.g. =VLOOKUP(F6,Hiport!E:J,6,0)

My understanding was LOOKUPs only work if the data you are searching is in order?

Thanks
William

 

[lenze]

Hi there, I am using 0

e.g. =VLOOKUP(F6,Hiport!E:J,6,0)

My understanding was LOOKUPs only work if the data you are searching is in order?

Thanks
William

For VLOOKUP, that is true ONLY if the 4th argument is "TRUE". A "FALSE" argument mandates an EXACT match, so the order is not relevant!
lenze

 

[thehungarian]

Hi Lenze

Thanks very much, this helps a great deal. For what it's worth, I am now using the following columns for the formatting of the sedols.

Column E Column F Column G Column H
4735573 =text(E2,7) =left(F2,7) if(G2="7",E2,G2)
B2346F1 =text(E3,7) =left(F3,7) if(G3="7",E3,G3)

this appears
Column E Column F Column G Column H
4735573 7 7 4735573
B2346F1 B2346F1 B2346F1 B2346F1

Column H then becomes the first column in the table array for the vlookup.

Thanks again
William