I have a set of excel spreadsheets that contain monthly sales data
(client name, purchase amount).  Each year and vertical has it's own
document, and within each document the 12 months each has their own
tab and list of sales for that month.  The format of the spreadsheet
has changed slightly throughout the last three years.

I want to form a single text file with a single list of each sale
(purchase amount, client, month/year).  What is the easiest way to do
this?

---

Request for Question Clarification by maniac-ga on 02 Mar 2004 15:42 PST

Hello Teflerdean,

Hmm. An interesting problem. My first thought goes to writing an Excel
macro to handle the three types of files (assuming one change per each
of the last three years) and generating a large spreadsheet with all
the data. This would work only if the total number of sales is less
than the limt of Excel (varies by version, recent versions have a
limit of about 65k rows).

Another approach would be to do subsets of the data and then combine
them separately.

Not sure if either would be "easiest", but I could certainly expand on
either approach if you like. Writing the macro would take a little
more effort and a lot more information (either sample files or explain
the formats of each file format). Please advise.

  --Maniac

---

Clarification of Question by teflerdean-ga on 04 Mar 2004 08:28 PST

There are approximately 2000 total transactions.

How do I go about writing a macro to do this?

If you've only got 2000 records this should be easy - I hope I
understand your problem correctly.

When you say that the format has changed slightly over the last 3
years, I assume that additional columns have been added / inserted.

You have 3 spreadsheets, our goal is that you end up with a 4th
spreadsheet with all 2000 records which you then save as a .csv (comma
seperated value) text file.

1. Make backups of your spreadsheets
2. Make them all look the same e.g. if you have inserted a column in
the new one's then insert the a column (insert menu, then column) at
the same place in the old one's.  I presume that it will be ok for you
to leave this empty.
3. Highlight all the fields in the sheet and copy/paste into your new
spreadsheet.  Repeat this until you have pasted/appended your original
3 into the 4th.
5. Choose File / Save as.  Select "save as type" csv.

Hope this helps.