This is a guest post by Vijay, our in-house VBA Expert.
Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.
Consolidate Data Demo
First, lets take a look at the consolidate data VBA code.
Consolidating Data from different Excel files – the setup
There is one master file (or sheet) which needs to be consolidated by pulling data from multiple source files containing raw data (having the same data structure).
Lets try to make a generic consolidation macro so that we can use this almost anywhere.
We start of by creating a simple table on our sheet, we will call this List.
- On this table essentially we are defining everything that our VBA code needs to know to copy and paste data.
- We start by telling the name of the Excel workbook and then the complete path (location) of the file.
- In the next 2 cells we define what are the starting cell and the ending cell that contains our data.
- Next we are put the name of the worksheet where the data will be pasted. In our example the sheet remains the same however as per your requirements you may put a different sheet name.
- The last option is to specify where to paste the copied data and we only need to tell the start cell address, the code will automatically select the next empty cell in that column and then paste the data from that point onwards.
Let’s understand the code.
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As StringstrListSheet = “List”
On Error GoTo ErrH
Sheets(strListSheet).Select
Range(“B2”).Select
‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> “”
strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Range(strCopyRange).Select
Selection.Copy
currentWB.Activate
Sheets(strWhereToCopy).Select
lastRow = LastRowInOneColumn(strStartCellColName)
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub
ErrH:
MsgBox “It seems some file was missing. The data copy operation is not complete.”
Exit Sub
End Sub
We have used the Workbook object to accomplish this task and also the Error handler to trap any errors that may come in case any file is missing.
The current code will display a message box when it is not able to open any file and will stop.
We start by assigning the workbook where we want to consolidate the date to the variable currentWB by using the statement:
Set currentWB = ActiveWorkbook
After this a looping construct has been used to go through all the inputs provided one by one and open the workbooks, it has been assumed these workbooks to contain on the data that we need to copy hence I did not specify the source sheet name, however this can be easily added to this code to add more functionality.
Inside our loop are the 4 variables which are assigned the
1) File name,
2) Copy Range,
3) Where To Copy and
4) Which Column contains the starting cell to paste data.
We open the data workbook by using the Application.Workbooks.Open method.
Once we have our first data workbook open, we assign this to the dataWB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.
Next we select the data that has been assigned to the copy range and copy to the clipboard.
We then switch back to our main workbook and select the sheet where we want to paste the data, I have assigned this to the variable called “strWhereToCopy”. This allows us to paste data onto separate sheets within the same workbook.
I have also made use of UDF (user defined function) to find the last cell in the column that we specify.
Once we have found the last row we then select the next empty cell below that and paste our data then.
Additional things that may be used to enhance this code
1. Since we are using the same instance of Excel we may allow the user to preserve the format of the data being pasted.
2. Allow the user with the option to clear data before new is pasted.
Download Consolidate Data from different files Demo file
Click here to download the workbook.
Please Note: You would need to create the data files on your system, this download only contains the code template to consolidate.
More on VBA & Macros
If you are new to VBA, Excel macros, go thru these links to learn more.
- More Examples on Consolidation
- What is VBA & Macros? Introduction
- Excel VBA Example Macros
- VBA tutorial videos
Join our VBA Classes
If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.
12 Responses to “Analyzing Search Keywords using Excel : Array Formulas in Real Life”
Very interesting Chandoo, as always. Personally I find endless uses for formulae such as {=sum(if(B$2:B$5=$A2,$C$2$C$5))}, just the flexibility in absolute and relative relative referencing and multiple conditions gives it the edge over dsum and others methods.
I've added to my blog a piece on SQL in VBA that I think might be of interest to you http://aviatormonkey.wordpress.com/2009/02/10/lesson-one-sql-in-vba/ . It's a bit techie, but I think you might like it.
Keep up the good work, aviatormonkey
Hi Chandoo,
You might find this coded solution I posted on a forum interesting.
http://www.excelforum.com/excel-programming/680810-create-tag-cloud-in-vba-possible.html
[...] under certain circumstances. One of the tips involved arranging search keywords in excel using Array Forumlas. Basically, if you need to know how frequent a word or group of keywords appear, you can use this [...]
@Aviatormonkey: Thanks for sharing the url. I found it a bit technical.. but very interesting.
@Andy: Looks like Jarad, the person who emailed me this problem has posted the same in excelforum too. Very good solution btw...
Realy great article
"You can take this basic model and extend it to include parameters like number of searches each key phrase has, how long the users stay on the site etc. to enhance the way tag cloud is generated and colored."
How would you go about doing this? I think it would need some VB
Hi,
I found the usage very interesting, but is giving me hard time because the LENs formula that use ranges are not considering the full range, in other words, the LEN formula is only bringing results from the respective "line" cell.
Using the example, when I place the formula to calculate the frequency for "windows" brings me only 1 result, not 11 as displayed in the example. It seems that the LEN formula using ranges is considering the respective line within the range, not the full range.
Any hint?
@Thiago
You have to enter the formula as an Array Formula
Enter the Formula and press Ctrl+Shift+Enter
Not just Enter
Thank you, Hui! I couldn't work out how this didn't work
is there a limit to the number of lines it can analyse.
Ie i am trying to get this to work on a list of sentances 1500 long.
@Gary
In Excel 2010/2013 Excel is only limited by available memory,
So just give it a go
As always try on a copy of the file first if you have any doubts
Apologies if I am missing something, but coudn't getting frequency be easier with Countif formula. Something like this - COUNTIF(Range with text,"*"&_cell with keyword_&"*")
Apologies if I missed, but what is the Array Formula to:
1. Analyze a list of URL's or a list of word phrases to understand frequency;
2. List in a nearby column from most used words to least used words;
3. Next to the list of words the count of occurrences.