Second time lucky, i hope..... spent best part of an hour only to be greeted by a message telling me to log in again and to find the post had been deleted.. arrgghh!!!
In a nutshell at the minute I am working with my colleague trying to implement a new computer system at work. The original system was DOS based and used two separate databases, Accs and TSHTCUST. the new system (using MySQL) requires that both databases are merged, and I have been left with the unenviable task of trying to get the two sets of data into one set of boxes.
The data collected in both sets is as follows:
Customer Code:
Customer:
Address 1:
Address 2:
Address 3:
Address 4:
Address 5:
Post Code:
Invoice Code:
Website:
E Trader Y / N:
Default VAT Code:
Pro FormaY / N:
Pro Forma Reason:
Aerospace Y / N:
Currency ID:
HT Flag:
HT Contact:
HT Phone:
HT Fax:
HT Contact2:
HT Phone2:
HT Contact3:
HT Phone3:
HT Contact4:
HT Phone4:
TS Flag:
TS Contact:
TS Phone:
TS Fax:
TS Contact2:
TS Phone2:
TS Contact3:
TS Phone3:
TS Contact4:
TS Phone4:
Email
Email 2
Email 3
Status:
The original data has been exported into an excel workbook and i have created a dynamic look up using the following code with "B4" being the drop down.
This checks both sets of data and then displays them side by side so as to enable me to create TWO compound datasets to include the following:
DATASET 1
Customer ID:
Customer:
Address 1:
Address 2:
Address 3:
Address 4:
Address 5:
Post Code:
Aerospace Y / N:
Web:
Currency ID:
Default VAT Code:
E Trader Y / N:
Invoice to Code:
Invoice to E-mail Address:
Pro Forma Y / N:
The reason behind this is because their are multiple occasions where the data has been entered into the wrong box originally.
The table below would obviously be replicated as per contact.
DATASET 2
Customer ID:
Name:
Telephone No:
Mobile No:
Fax No:
Email Address:
System ID:
In order to get the information into Dataset 1 I have used various if commands i.e:
I then created a macro (see below) that will copy the data, paste special (as values), recopy, transpose and paste into another sheet in the next available row.
i am now trying to find a piece of code that will enable me to separate Mobile/Cell phone numbers from Land line phone numbers
i have used the following function to clean all the numbers to make them read as uk:
due to the nature of the information in the databases it is not possible for me to supply a copy of the work book and putting in dummy info would be a complete nightmare.
Any help would be greatly appreciated.
Thanks
Jez
Bookmarks