After reading and playing around with Filters, Consolidation, Duplicates, VLOOKUP, etc., and searching this Forum I still need some guidance on how to approach my issue.
First, I use Excel 2011 for Mac under OS 10.7.5 but also have access to a windows machine running Excel 2010 and Windows 7. I do not have macro writing skills.
SUMMARY: I have 2 spreadsheets containing information about wines that are subsets of each other (each file has wines not listed in the other). Into the “Master” (most recent) file I would like to insert rows from the second file that are currently not in the Master.
DETAILS: I track information about wines by vintage year in separate files. Currently I have files for 2010 and 2011 vintage years. The structure of the 2 files is the same. They were built with the Excel 2010/11 table capabilities so each column has a filter, and since 2011 was originally a copy of 2010 the headings are the same. Both files have the same table structure in each of 3 worksheets in each file containing info by wine types (Red, White, Dessert). Both files have about 700 wines with about 100 unique wines in each file.
Column A contains the name of the Appellation the wine is from. Column B contains the name of the Wine. The last column contains Comments about the wine and/or vineyard. The remaining columns contain numeric information about the wines. Examining Columns A and B determines a unique wine entry since wines from different appellations can have the same name. Each worksheet within each file is sorted by Appellation and within that by Wine.
I have taken a copy of the 2010 file and removed the numeric data from each worksheet leaving only the textual entries in Appellation, Wine, and Comments.
I need to figure out which wines in the 2010 file are not in the 2011 file and insert them into the 2011 tables in each of the 3 worksheets. I need to retain all data currently in the 2011 file. I will use the updated 2011 file to add further data about the 2011 wines and as a master template to maintain for use in subsequent years.
If it is easiest to append the unique rows from the 2010 file to the end of the 2011 tables that is fine since I can use the table filters to re-sort the wines within appellation easily.
Ideally a solution between visual inspection with copy and paste, and learning VBA, is possible. Suggestions on the best approach are requested.
Bookmarks