Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a (huge) Excel 2007 workbook with three worksheets, representing one of three major astronomical catalogs (I need a large dataset for another project I'm working on). Due to the size, I can't do this manually.

I need to consolidate the three worksheets into a single worksheet, where each worksheet may or may not have matching data in one of the other worksheets. Each sheet has the same headers, in the same order.

The first 6 columns contain the names of the object from the catalog:


Each of these columns may or may not be populated, and may be duplicated in other of the other sheets (especially HDNAME and BDNAME).

Can someone suggest how to consolidate these items into a single worksheet, eliminating duplicates, etc?

share|improve this question
It may be possible to do this by importing into an Access table, removing the duplicates, then exporting to a spreadsheet. That should require little more than the wizards already in place in Access. – music2myear Oct 18 '11 at 13:58

How many rows would you have, including duplicates? If it's less than the Excel 2007 limit of 1,048,576 then you could simply cut/paste the rows from each sheet to one master sheet to consolidate them. You'd then just need to remove duplicates; in Excel 2007 you should be able to use method 1 on this page.

share|improve this answer
I ended up taking music2myear's advice and going via Access. The final table has 190K+ rows in it. – Jason Oct 18 '11 at 15:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .