Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting file to specified format
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting file to specified format
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 On Jun 16, 7:44*am, Chris wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting file to specified format
Thanks Pete - very helpful...now done thanks to your help.
"Chris" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting file to specified format
You're welcome, Chris - thanks for feeding back.
Pete On Jun 17, 12:26*am, Chris wrote: Thanks Pete - very helpful...now done thanks to your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
Converting Excel file to text delimited file | Excel Discussion (Misc queries) | |||
Converting excel file to tab delimited (txt) file | Excel Discussion (Misc queries) | |||
converting PDF file to readable excel format | Excel Worksheet Functions |