I would really appreciate any help in building the following workflow.
I have two files of data:
- A CSV file that contains about 250 rows of transactions. Each row details 10 columns of common data about each of the transactions. Included is the account number which is populated and the customer code which is an empty field.
- I also have a separate Excel workbook that contains two columns of data that act as a ‘mapping table’ between an account number and the corresponding customer code.
I would like to achieve the following:
I would like to select the account number for each of the 250 transactions on the CSV file and look up whether it exists in the excel mapping table.
• If it does exist, I would like to copy the corresponding customer code from the mapping table and update it into the corresponding empty column cell of the transaction in the CSV file. Also, if the customer code does get updated, then I would like to update another field in the corresponding column cell with a static data value of “closed”.
• If the account number does not exist in the mapping table then no action should be taken for that transaction, and the next transaction should be interrogated in the same fashion.
This should be repeated for each row up to and including the last transaction in the CSV file.
Many thanks in anticipation of your kind assistance.