Converting file to specified format

[Chris]

Is there a simple method of converting a file received in one format into
another on a regular basis (ie every month)?

For example:

File 1 has data in columns A to G:
Emp ID
Employee
birth_date
Account #
Total
Vol
Total

I need to convert this into file 2 with columns A to F where the "Employee"
column in file 1 needs to be split into SURNAME and FIRST NAME and appear in
the following format:

MEMBER ID
SURNAME
FIRST NAME
TOTAL
VOL
D.O.B

Any ideas greatly appreciated.

 

[Pete_UK]

Set up a "Master" file which has two sheets. Sheet1 is where you will
copy your monthly data into, so you can set this up with headings only
in columns A to G. In Sheet2 you will have the headings in A1:F1 and
in row 2 you can have formulae to get the data from Sheet1 in the
format you require. You can use LEFT, MID and RIGHT functions to
extract surname and first name (depending on how these are laid out in
your first file) and simple linking formulae for the other columns.
These formulae can be copied down for as many rows as you think you
will need (or just have them in row 2 and copy down as required each
month when you copy the data into Sheet1).

So, the operation would be to open the Master file, open the newly-
received file, copy data into Sheet1 of the Master file, copy formulae
down rows of Sheet2 (if not already in place), fix the values in
Sheet2, delete Sheet1, use File | Save As to save the file with a
different name (the Master file does not get changed, so can be used
for the following month).

Of course, all of this could be automated with a simple recorded
macro.

Hope this helps.

Pete

 

[Chris]

Thanks Pete - very helpful...now done thanks to your help.

 

[Pete_UK]

You're welcome, Chris - thanks for feeding back.

Pete