Combine 7 Spreadsheets into 1

[Spacle]

I am very new to Access and I can't seem to figure out what I need to do. Here is my project.



I have 7 spreadsheets, all have different information on them about envelopes. Each spreadsheet has a column for the envelope (each envelope # column on each spreadsheet has the same column heading). Each spreadsheet tells of the progress of the envelope through a specific stage of the process (ex. spreadsheet 1- when did it get imported spreadsheet 2-when did it get emailed spreadsheet 3-when did it get processed, etc.). Because the envelopes are in varying stages of completion, an envelope may be on one spreadsheet but not another (which is why the Matched records query doesn't work).

I need to end up with one spreadsheet that has all envelopes on it. Each envelope needs to be on its own row with whatever information is applicable to that specific envelope from any/all of the 7 spreadsheets. I tried to do a "Merge Spreadsheets" in Excel, but that only imports numbers (because of the Sum function you have to use) so I lost all comments, envelope statuses (ex. Processed, In Data Entry, etc.) so that didn't work either.

I know I could do this manually but there are upwards of 500 envelopes and I don't want to have to hunt and peck for each envelope number on all 7 spreadsheets. I was also given the suggestion that I do a Matched query, then an unmatched query and combine the outputs together, but I would have to bounce each spreadsheet off the other 6 and that seems worse than doing it manually. I feel like there has to be a way to do this easily and just can't seem to find it.

Any help would be greatly appreciated

[ssanfu]

I've gotten away from Excel, so I don't know how much help I will be.
And I'm not sure if you are trying to convert the spreadsheet to a database.

I need to end up with one spreadsheet that has all envelopes on it.

Do you have 7 workbooks with 1 sheet each or 1 workbook with 7 tabs?

If there is 1 workbook with 7 tabs, you could create an 8th tab (Summary) and write code to gather all of the envelope numbers onto the Summary sheet, then search each of the 7 sheets for the data you want.

If you are trying to convert to Access, we need to know more about your project.
Describe what you are trying/want to do. Pretend you are in line at Burger King - tell me about the project.

What have you tried as far as the table designs?
What are the headers for each of the 7 sheets?
Sample data??

If it is easier, provide a few rows from each sheet, zip it ad attach it to a post.

[June7]

Are the sheets structured simple enough so that Access can link to them (or import into tables) and they will display as tables in Access?

Linked worksheets can be manipulated just like Access tables (except can't edit the sheets). Build queries with the linked sheets.

Will need a 'master' list of all envelope numbers. This can be generated with a UNION query that combines the envelope numbers from all 7 linked worksheets. The UNION will show only unique numbers, will not allow duplicates. Then join the 7 sheets to the 'master' dataset.

[rpeare]

Can you supply a sample of the spreadsheet itself, doesn't have to be real data other than it has to mimic your current process.

[Dal Jeanis]

Access can help you do this. But were you looking for an Access solution?