Combining multiple similar database files

[jds217]

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

 

[nbrcrunch]

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]

Thank you!!!

Nbrcrunch --

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

John