Sedol help - formatting numeric and alphanumeric data

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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the board!!!
What is the 4th argument in your VLOOKUP formula? It should be 0 or FALSE.
Code:
=VLOOKUP(A1,C1:D100,2,FALSE)
No need to sort
lenze
 
Last edited:

thehungarian

New Member
Joined
Apr 9, 2009
Messages
3
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Apr 9, 2009
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,566
Members
414,078
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top