PDA

View Full Version : Matching variable formats



intheblack
08-05-2008, 10:58 PM
Hi.

I would like some more assistance if possible with this spreadsheet. I posted not to long ago and got some great help, but more issues are appearing. So here goes;

I have attached a sample sheet.
Basically it is a trade sheet where I record trades for our clients (tabs PT1,PT2 etc etc) The S&P and list tabs are only reference tabs.

In PT1 I will record the data for the client. At the end of the trading day, I download contract notes (to cn) which I have to match to each client, specially the amount of units bought or sold and the price (PT1 col O & P to match cn Col m & N)

Previously I was assisted in being able to match cn and PT1 by using runners (see cn col a and PT1 col AD) and a vlookup. That's all fine.

Here are the issues;
When I download the contract notes into cn , the broker doesn't always type the client number correctly (Col AC).When I put them into PT1 all client numbers have the same format (0154160-D2-01)(7 digits a dash a letter and number - a dash and two numbers) .

The brokers ones may be missing one or 2 0's at the beginning of the number or they might forget to put the dash in, for example (15476-ME-01, 0015119D9-01, 0015119D901)

If you look at PT1 from rows 18-28 in col's O & P they aren't matching due to this problem.

So the first question would be is it possible to get a formula to match regardless of any of those issues? Bearing in mind that the client number is a essential part of the matching process.

The second question is when there is a match, ( for example PT1 rows 6-16) to highlight the corresponding rows in cn as I normally would do manually. note - I've tried conditional formatting with named ranges and for some reason it won't let me reference another worksheet.

Any help would be great.

Cheers