For example, you can use the addition formula to find the average of those values.Summarizing information by using formulas is handy because you can update the summary calculation simply by recalculating the worksheet.Excel's Consolidate feature's claim to fame is merging and summarizing values from multiple workbooks.It's a great tool for combining data when several users work with different instances of the same file.Consolidate According to the Position in an Excel Worksheet Identify Categories to Consolidate Excel Data Use Formulas to Consolidate Excel Data Access the Pivot Table function Community Q&A Microsoft Office Excel comes with several features for customizing tables and charts full of important data.The program also offers efficient ways to combine and summarize data from multiple files, also known as worksheets.What is the most efficient way to get it so that I have all of that data consolidated into one column (I. - 80,000 rows of data in column A instead of 20,000 rows of data spread out across 4 columns). What I mean is, if your solution isn't a "formula" but VBA, how do I implement that solution? Because I can have up to 30 or 40 columns with all of them having 20 to 70 thousand entries. 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.Copy & pasting becomes quite tedious...having to find where each columns data stops, clicking it, scrolling back to top, selecting all, copying, going back to the bottom of column A, pasting...repeat 30 or 40 times. 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.
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.
If you use the RExcel plugin above, you can fire all of this up and run it within Excel itself.
The R community is very active and can help address any and all questions you may encounter. placed into each cell of your second workbook will retrieve the appropriate cell from the source sheet.
A script to reformat the data may well be more efficient, but 20k rows is no longer a real limit in a modern Excel workbook. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?