Need to merge excel files with different column headings

[rob_ford16]

Hello,

I'm new here so please forgive any naivety. I have about 100 excel workbooks that contain essentially the same types of data. Each file details plant collections of certain people and each plants corresponding habitat, date collected, locality, county, state, species etc. Yet they have many different column names for the same things (I.E., date collected = collection date = Col_date = coll_date)

How could I go about combining all of these files into one, while keeping the columns matched up? I want the data in File #1 under the column heading "Date Collected", to be under the same column as the data from File #2 with a column heading of say, coll_date.

I've imported all of these into Access as separate tables and thought maybe I could use SQL to do something but I just am not sure what to do.

Besides copying and pasting for hours on end, I have no real solution to this problem.

Any help is much appreciated!

 

[BlueHornet]

It sounds as if you're attempting to do two things:

1. Combine all of the separate workbooks into a single workbook with multiple worksheets, and

2. Make all of the worksheets have the same appearance, field names, etc.

Is that right?

I'm sort of pseudo-coding here based on that assumption, but ...

Start a loop and Do while unprocessed files exist.

Open the first file to be combined and "move" worksheet to the end of the "collector" workbook file.

Save the "collector" workbook file.
Close the "collected" file without saving changes (lets you retain the original as a backup in case things go wrong later).

Repeat the loop, going to the next file.

Exit the code.

Now you'd have a single file with copies of each of your singleton worksheets.

Assuming the data arrangement is all the same (is that too big an assumption?) there are simple ways to rename all columns to be the same:

Click the first tab in the workbook while holding the Shift key to select all of the worksheets at once. Changes you make in the field names on any one of the selected worksheets should apply to all of them at the same time. (But be careful. Test this functionality on some test / junk worksheets to see that you know how it works. The functionality seems to have changed somehow between Excel 2000 and 2010 -- I'm just getting used to 2010 and the performance in this area seems different.)

On the other hand, if I've misunderstood what it is that you're asking, then please clarify and I'll see if I can help.