How do I combine multiple databases in excel to list information below each other??

Question

I have a number of identical databases I would like to combine to show all of the data at once. Each database captures peoples names in column A, and information about the person going across in the row from their name.

Each database contains the people from one team and I would like to combine these databases so I can see the people from all of the teams in one database. So for example I want to see all of the people and information for team one, then below that all of the people and information for team two.

I have learnt a fair bit about excel recently but have got stuck with this. I know I can manually copy the information using =, but there are too many databases and rows for this to be possible. I have tried to do this using the combine and merge function, but this seems to be overwriting some of the information, for example if the biggest team has 20 people in it I can only get it to display a maximum of of 20 rows.

Any suggestions gratefully received, I'm using excel 2003.

Answer 1

Keep in mind that Excel isn't designed to be a database which what you're trying to use it for.  Your application would be better suited for a true database program such as Access or SQL, etc.  With that being said, there really isn't any "function" that is going to allow you automatically combine data from multiple sheets into one sheet.

If a database program isn't possible, then my suggestion is

1. in each sheet, insert a new column and call it "Team #".  

2. Enter the persons team # in this column for all members.

3. Combine all of these individual sheets into one large sheet (you'll have to do it manually by copying/pasting them into one "master" sheet).

4. Only use this new master sheet from now on.  Just add new members to the bottom (or top) of the list and then just re-sort based on the Team # column.

sorry, probably not the answer you were hoping for...

Answer 2

What you are saying is that you have several workbooks and each workbook represents one team, correct?

Here is what you need to do, assume that all workbooks have 20 people for this example:

1. Open Book1 and go to the first available cell in A Column (assuming 20 people and a header row this should be cell A22)

2. Open Book2 and select just the rows you need (assuming 20 people and a header row this should be rows 2 through 21). You can select those rows by clicking and dragging on the row numbers in the column to the left of the A Column.

3. Hit Crtl + C to copy (or right click in the selected area and hit copy).

4. Return to Book1 and paste into cell A22.

5. Go to the first available cell in A Column (this should now be cell A42).

6. Close Book2.

7. Repeat from line 2. as necessary until all information is compiled.

In the future you should look into maintaining all of this information in one sheet from the beginning and use Excel's Filter ability to hide the records you don't want to see.