Reformatting columns, creating files, and totalling organized data

Hey guys. I am an IT tech at a company. Unfortunately I never had any experience with creating macros or visual basic, and as a result of both of our programming guys disappearing this week, I've had a lot of work dumped on my shoulders. One thing my boss has asked me to do is alter this file, creating a bunch of macros (I believe? It doesn't seem like I've been able to create substitute formulas to work for the first part dealing with LName FName MI). I was wondering if there was anyone out there who could help me with this. I've uploaded the xls file (It reads much better in .csv extension though) and I'll list what I've been asked to do. I'm not necessarily asking anyone to do this for me, but I have 2 other tasks I've been asked to complete, one in Java, one in SQL and learning them on top of macros has been rather intense. Any insight or redirection to a reliable source to quickly teach me would be greatly appreciated, thank you!

2) reformat the 'Name' column to have 'Last Name, First Name Middle Initial.'
3) reformat birthdays to be YYYYMMDD
4) output reformatted data to a file called : 'cleandata.<time stamp YYYYMMDD_HHMMSS>.csv'
5) have output sorted alphabetically by state, then by name
6) any duplicates account numbers should be rejected and noted
7) produce a secondary report named 'summaryreport.<time stamp YYYYMMDD_HHMMSS>.txt' with :
- total balances aggregated by state
- there is a column "Favorites". List the top five favorites and the number of times
they appear.

Hi madfrog

The file you attached appears to be a coma delimited text file. Is this indeed what your data looks like?

John

Hi madfrog

In the event someone else is working on this, 02/29/1974 is NOT a valid date. Apparently 1974 was NOT a leap year. Just a heads up.

John

Hi madfrog

If duplicate Account Numbers exist what do you want to happen? Should BOTH be rejected and noted? Or should only one be rejected and noted?

John

Hi Ben

Working on it as we speak. What about this question

If duplicate Account Numbers exist what do you want to happen? Should BOTH be rejected and noted? Or should only one be rejected and noted?

John

Hi Ben

Attached is the first draft. It accomplishes these items:

1. reformat the 'Name' column to have 'Last Name, First Name Middle Initial.'
2. reformat birthdays to be YYYYMMDD
3. output reformatted data to a file called : 'cleandata.<time stamp YYYYMMDD_HHMMSS>.XLS (note the fie extension)
4. have output sorted alphabetically by state, then by name
5. any duplicates account numbers should be rejected and noted
6. produce a secondary report named 'summaryreport.<time stamp YYYYMMDD_HHMMSS>.XLS (note the file extension) with total balances aggregated by state
7. there is a column "Favorites". List the top five favorites and the number of times they appear.

There are some formatting issues but I'd like you to see if I'm on the right track.

John

Hi Ben

Well, I've broken it. I'll fix the problem but let me know what else you see that needs changed.

John

Hi Ben

Well, I've fixed that. I've another issue with duplicate records. An incorrect record appears to be deleted. I'm on top of it. Get back to you.

John

Hi Ben

I've been over this version pretty throughly. I'd like you to beat it up and see if you can break it. Also, let me know what further enhancements you require.

I'll continue to look at it; if i see issues, I'll let you know. Please do the same.

John

I also wrote a program to help Madfrog AKA Ben.

This one lets the user chosse the CSV data file and then writes a text file for each report.

Gordon