Consolidating excel worksheets

6854933580_2c8b688306_z

Occasionally a client asks me to create a shared workbook in Excel, so two or more employees can work in it at the same time.

It sounds good in theory, but I always try to come up with a different solution.

When the team updates, they can update the following.

(They can change the values to anything in columns E-I [in the 2nd workbook, I've changed values in columns E and G], they can add/remove rows, [in the 2nd workbook, I've added row 42 "Denny's-Pizza Hut...], etc) I have to go and download the base workbook at the end of every week and then compare line by line and update the master with any changes.

If you need to perform many statistical calculations on your data, I would recommend moving out of the Excel environment into something that is more directly suited for data manipulation and analysis, such as R.

Press Alt F11 to open the VBE Press Control R to view the Project Explorer Navigate to the project for your workbook and choose Insert - Module Paste this code in the code pane Best and Simple solution to follow: Select the range of the columns you want to be copied to single column Copy the range of cells (multiple columns) Open Notepad Paste the selected range of cells Press Ctrl H, replace \t by \n and click on replace all all the multiple columns fall under one single column now copy the same and paste in excel Simple and effective solution for those who dont want to waste time coding in VBA Here is how you do it with some simple Excel formulae, and no fancy VBA needed. Please see this example spreadsheet: https://docs.google.com/spreadsheet/ccc? key=0Au Sy DFZlc Rt Hd GJOSn Fw REot Rz Ff M28t WElp Z1Fa R2c&usp=sharing#gid=0 You didn't mention if you are using Excel 2003 or 2007, but you may run into an issue with the # of rows in Excel 2003 being capped at 65,536. Also, can I ask what your end goal is for your analysis?

Lets say I have an excel sheet with 4 columns of data & 20,000 rows of data in each column. If this code doesn't do what you want, the only way to go back is to close without saving and reopen. There are a variety of options for connecting R to Excel, including From there, you can perform any number of statistical or graphing operations.

This solution would work, however since the team can add and remove rows in the base workbook, if one row is added [ex.

After you share a workbook, many of Excel's features can't be used.

There's a list of unavailable features for Excel 2003 on the Microsoft site, and in Excel's help.

Maybe you've had success with shared workbooks, and I'd love to hear about it in the comments.

For me, the limitations far outweigh the benefits, and there's usually another way to accommodate multiple users.

You will need to modify the ranges in the COUNT function to match the maximum number of rows in the source sheet. If you need something other than a 0 for empty cells, you may prefer to include a conditional.

You must have an account to comment. Please register or login here!