Data Analysis in Excel - Combine data with VBA

Data analysis can be broken down into 3 key steps: preparing, analyzing and displaying data

Ensuring top-quality execution of data preparation is paramount to effectively understand the insights concealed within the numbers and crafting compelling narratives for our intended audience. 

This preparatory phase frequently involves the consolidation of data sourced from diverse spreadsheets. In this post, we present a clear, step-by-step guide on seamlessly pulling data from multiple spreadsheets into one file.

The challenge that will be solved in this post is to combine two lists by creating a third list featuring elements displayed in both original lists alongside with all the information reported in the two lists

We will use variables to take advantage of their processing speed as explained here.

The following picture reflects the result that will be achieved 

The code will run through the following steps:

• Define the variables

• Insert the lists into the variables 

• Perform two nested loops. Each item in the first list will be searched in the second list performing a loop on all elements of the second list

• If the item is found all the related data present in both lists will be inserted in the third list

In a workbook there are two sheets called X and Y

Each of the two sheets contain a list with elements in columns A and B.  

The code has to create the variables, identify the ranges containing the data in sheets X and Y and import the identified data in the variables 

Let's start by creating the variables that will be hosting the data

Sub combinedata()

Dim ListA () as Variant

Dim ListB () as Variant

Create the variables that will be used to run the loops and create the third list

Dim i as integer, h as Integer, m as Integer

Create the variables that will be used to identify the last used row of the two lists in sheets X and Y

Dim lstRowX as integer, lstRowY as Integer

In sheets X and Y the ranges containing the data need to be identified

Therefore the last used row is identified in order to select the perimeter of the range hosting the data

Once the perimeter is defined the full range is imported in the variables

The same steps are performed for sheet X and sheet Y

Sheets("X").select

' The next line identifies the number of the last row and records it in the lstRowX variable

lstRowX = Cells(Rows.Count,"A").End(xlUp).Row

ListA = range(cells(1,1),cells(lstRowX,2)).value

Sheets("Y").select

lstRowY = Cells(Rows.Count,"A").End(xlUp).Row

ListB = range(cells(1,1),cells(lstRowY,2)).value

The code performs the nested loops in the following order:

1. The first item of List A is identified

2. All the elements of ListB are scanned to see if there is one which is equal to the selected one in ListA 

3. If the same item is identified in ListB then the code writes to sheet Z all the information contained in ListA and ListB related to the matching item 

It is important to note that the element used to identify elements in ListA and ListB is the item ID contained in the first column The item ID

is unique ( i.e. it does not repeat within the same list)

The counter for the rows of the third list is initiated and any data in Columns A:B of sheet Z are cancelled

m = 1

Sheets("Z").select

Columns("A:B").select

Selection.clearcontents 

The first loop is initiated

For i = 1 to lstRowX

The second loop is initiated 

For h = 1 To lstRowY

Items of the two lists are compared and if the element in the first column is the same all the information of the corresponding record is copied to sheet Z 

If ListA (i,1) = ListB (h,1) then

' The first elemtn of ListA is copied to sheet Z to start creating the third list

sheets("Z").cells(m,1)= ListA(i,1)

' The second element of ListA is copied to sheet Z 

sheets("Z").cells(m,2)= ListA(i,2)

' The second element of ListB is copied to sheet Z. The first one is equal to the one in ListA therefore

' no need to copy again

sheets("Z").cells(m,3)= ListB(h,2)

' Variable m is increased by one so that if other records have equal ID, the data is copied to the next row 

m = m+1

' The code moves to analyse the next item of ListB

Next h

' When all the items of ListB have been analysed the code moves to the next item of ListA

Next i

' The variant variables are canceled in order to free up memory

Erase ListA 

Erase ListB

End Sub