Excel 2007
I would like to combine a number of spreadsheets that are very different but have one common column: Course number. All the course numbers are from 3 to 5 digits and are of the same data-type in all spreadsheets (numeric, general).
Each spreadsheet has different information about courses (mostly text and dates) that may have different cell formatting. All were created by different people at different times for different reasons. None of the spreadsheets have exactly the same list of course numbers. All the spreadsheets contain the majority of the course numbers but some may be missing some numbers, and others may have more numbers.
My goal is to combine all the data for each course number into one spreadsheet. Obviously some courses will be missing data because that course number was not on the particular spreadsheet that had that data.
So the final spreadsheet would have all the unique courses numbers (from all the spreadsheets) and all the columns of data from those same spreadsheets.
A simple example is this: One spread sheet has the course numbers 1,4,5,6,9, and has the name of the course owner for each course. Another spreadsheet has courses 1, 3,4 6,7,8,9 and has the date the course was last reviewed.
The final spread sheet would have the course numbers 1,3,4,5,6,7,8,9 and have column for the course owner and one for the date it was last reviewed. Of course some cells would be blank since there is no data.
Of course the real problem is much more complex with 600 course numbers and about a dozen spreadsheets, each with multiple columns. Which is why it can't be done by hand.
I have tried Vlookup but it will not work because the list of course numbers in each spreadsheet is not identical. As soon as it gets to a number that is not in the same position in both spreadsheets it starts providing the dreaded "#NA." I also tried consolidate, but that was a nonstarter.
I would like to combine a number of spreadsheets that are very different but have one common column: Course number. All the course numbers are from 3 to 5 digits and are of the same data-type in all spreadsheets (numeric, general).
Each spreadsheet has different information about courses (mostly text and dates) that may have different cell formatting. All were created by different people at different times for different reasons. None of the spreadsheets have exactly the same list of course numbers. All the spreadsheets contain the majority of the course numbers but some may be missing some numbers, and others may have more numbers.
My goal is to combine all the data for each course number into one spreadsheet. Obviously some courses will be missing data because that course number was not on the particular spreadsheet that had that data.
So the final spreadsheet would have all the unique courses numbers (from all the spreadsheets) and all the columns of data from those same spreadsheets.
A simple example is this: One spread sheet has the course numbers 1,4,5,6,9, and has the name of the course owner for each course. Another spreadsheet has courses 1, 3,4 6,7,8,9 and has the date the course was last reviewed.
The final spread sheet would have the course numbers 1,3,4,5,6,7,8,9 and have column for the course owner and one for the date it was last reviewed. Of course some cells would be blank since there is no data.
Of course the real problem is much more complex with 600 course numbers and about a dozen spreadsheets, each with multiple columns. Which is why it can't be done by hand.
I have tried Vlookup but it will not work because the list of course numbers in each spreadsheet is not identical. As soon as it gets to a number that is not in the same position in both spreadsheets it starts providing the dreaded "#NA." I also tried consolidate, but that was a nonstarter.