up vote 1 down vote favorite
share [g+] share [fb]

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:

HDNAME GCNAME SAONAME BDNAME HIPNAME GLIESENAME

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?

link|improve this question
1  
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
feedback

1 Answer

up vote 2 down vote

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.

link|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
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.