Combining multiple similar database files

jds217

New Member
Joined
Nov 6, 2005
Messages
12
OK, another library-related question. Bear with me as I try to explain.

I have multiple Excel files, each containing a list of books for a reading program from a particular school. So each file contains columns like:
-- title
-- author
-- call number
-- readling level

So I may have a file from School A, one from School B, one from School C, etc.

Many of the books are duplicated on multiple lists.

What I want to end up with is ONE Excel file, with each book listed only once (no duplicates), but also with some indication of WHICH school list(s) it is found on. For example, I think it would work if I had a column for each school, and an "x" in that column if that book was on that school's list.

So I thought I could ADD a column to each separate list, like a "School A" column to the School A file, and fill it with "x" all the way down.

I know how to combine lists and filter for duplicates, but then I don't know how to retain the information about the particular school lists when doing so.

Any better ideas for combining this info in a useful, sortable way?

OR am I better off approaching this through Access? Say create a book table, and a school table, and each book entry could be tagged for multiple school lists?

And I should say I need to develop an AUTOMATED way to combine this information -- I have lists from a dozen or more schools, each with 4000+ books.

Hope that was clear. Thanks for any possible help!!

John
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
1. combine all lists into one but first add a column that specifies the school (file) it came from.

2. Select the data and create a pivot table with school names across the top and book names going down the left side. The data grid will also be books. Change to count books if needed (usually defaults to sum)
 

jds217

New Member
Joined
Nov 6, 2005
Messages
12
Thank you!!!

Nbrcrunch --

Thank you!!! With a little cutting and pasting, that worked great for me!

John
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,764
Members
412,291
Latest member
marypolitan
Top