Hi,

I need a bit of direction on the best approach to the task outlined below.

I have files of bank transactions. I need to add two codes to each transaction. One is the nominal account that the item will be posted to the other is the line in the cashflow statement that it relates to. 

There are two fields in the file that give some details of the type of transaction. I need to use the data in these fields to lookup to the codes that need to be allocated to the line. However the lookup could be either field and the content is not uniform so the bit that matches to the code will be in the field somewhere but not necessarily at the beginning and can't be all of the field.

Example 1: lets say the fields contain the following data:

Field 1 = NOREF

Field 2 = Remit 12345 codeAbc456

I know from reading this that 12345 is a creditor account. The nominal code would be purchase ledger control, the cashflow code would be Creditors.

Example 2: The fields are as follows:

Field 1 = NIC HMRC P8

Field 2 = Ref678901

From field 1 I can see that it contains HMRC and therefore this goes to nominal PAYE control and in the cashflow it goes to the HMRC - PAYE line.

So I need some means of doing a lookup of a string within a string but the string could be in two different fields. I realise that I will need to maintain a table of lookup values and the associated codes and this will need to be added to as new types of transaction come through. Any unidentified transactions would be labelled as "Not found" or similar.

Any help appreciated.

Thanks

Bax