Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an excel document with over 30 sheets. Each has the same layout: a simple table having the same columns, header/total rows, but different number of rows (none has more than 500 rows).

I want to combine them into a master table. One time operation only.

Manually, I could copy 30 table one after the other on a new worksheet. When done, I'd do a sort and delete any blank/header/total row. I don't care about the formatting. I guess it would take me about 15-20 minutes, but there always the risk I do a mistake and miss a sheet or something.

VBA sounds like it would take too much time. Right now I'm almost considering importing the workbook into SQL Server, UNION them and copy-paste the result back to excel.

Do you have any good tricks?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

If this is something you will need to do frequently then I recommend writing a quick VBA routine. If it is a one-time thing then I would just do it manually. To make sure you don't accidentally skip a table, use cut-paste instead of copy-paste.

Here is how I would do this. It goes fairly quickly with keyboard shortcuts.

  • Open the original workbook in Excel as read-only to prevent any accidental loss of data.
  • Open a new workbook to hold the new Master Table.
  • Close any other workbooks so it is easy to switch between the two you are working with.
  • Select the first worksheet in the original workbook that you want to copy.

Now you can start using keyboard shortcuts.

  1. Ctrl + Home to jump to the top left of the spreadsheet. (This will jump to A1 unless there are rows or columns frozen.)
  2. Shift + Ctrl + End to select everything left and down of the current selected cell.
  3. Ctrl + X to cut the data.
  4. Ctrl + Tab to switch to the new workbook.
  5. Double-check you are in the correct location and press Enter to paste the data.
  6. Ctrl + End to go to the last cell pasted.
  7. Down Arrow to go to the next unused row.
  8. Ctrl + Left Arrow to go back to column A.
  9. Ctrl + Tab to switch back to the original workbook.
  10. Ctrl + Page Down to select the next worksheet.
  11. Go back to step 1 and repeat until done.

When you are done, you can Ctrl + Page Up back thru all the original worksheets and verify all the data has been removed.

Assuming your title row isn't frozen this will copy the title row from all 30 tables. In that case either sort the master table to group all the duplicate title rows and delete the extras or use Data -> Remove Duplicates.

If you accidentally skip a step or paste over existing data, press Ctrl + Z to undo.

I just did a test following the above steps. The workbook I used had 10 worksheets each with 120 rows of data. It took me 1 ½ minutes to create the master table. If you aren't familiar with keyboard shortcuts it might take you twice as long. For 30 worksheets I would guess it might take you 10 minutes.

share|improve this answer
    
Thanks, keyboard-only definitely makes it easier - and safer. It is possible to record those steps in a macro and run it 30 times? –  mtone Jan 25 '12 at 1:24
    
For the most part. If Excel thinks the data tables are actual table objects then Excel will record a couple of the steps with the name of the table and you will have to adjust. The other thing to remember is to make sure to use relative references while recording. Otherwise step 7 will record as selecting the specific row you are moving to. –  mischab1 Jan 25 '12 at 2:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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