Combining exported data in Excel using VLOOKUP

In this guide you will learn how to reconcile data from related forms exported from Coreo using Microsoft Excel. In this example, we’ll combine records from a Preliminary Roost Assessment survey, and to make this easier to follow, we have supplied some sample data which forms the basis of this guide, but the same principles should work with your own surveys and records.

A video walkthrough of this process is also available.

Export your data from Coreo

The first step in this process is to export your data from Coreo. For this example, you can download the sample data but you can always export your own data from Coreo if you prefer (see Exporting records for a guide on how to do this).

Unzip the source file and you should have three CSV files, one for each form:

• preliminary-roost-building-information-1.csv
  • contains our building records
• preliminary-roost-exterior-prfs.csv
  • contains our exterior PRF data
• preliminary-roost-exterior-prfs.csv
  • contains our interior PRF data

Import your CSV data into Excel

Opening the preliminary-roost-building-information-1.csv file in Microsoft Excel, will create a new sheet from this file, creating columns for each heading. Rename this sheet to buildings.

Create a new sheet in Excel and rename this to PRF, and import one of the PRF CSV files. In this case as both of the PRF files happen to use the same structure, you can import both to the same sheet if you like. Otherwise create a separate sheet for each file and import one file per sheet and repeat the data combining process outlined below for each.

To import into Excel, use File > Import and choose CSV file, select delimited file, and use the comma delimiter.

You should now have two sheets populated with the records from the imported files.

Combine the records

Select the PRF sheet into which you have imported the PRF records. We’d like the building information to be first in this sheet so we need to make space here for the number of columns we wish to include from the buildings sheet. In this example we will include some initial data about the buildings, so right-click on column A and select insert, and repeat this until you have 5 new empty columns on the left of your sheet.

We now need to give these empty columns some headings, so from the buildings sheet, select the column headings you want to copy across, which in this example will be these five columns:

Building Name, Building ID Number, Building_Description, Env_Context, Ext_Survey_Limitations

and copy these into the PRF sheet like so:

We now need to populate these empty rows with data from the buildings sheet, and we will use the vlookup function to do this.

First, we will add the ‘Building Name’ value. Place your cursor in cell A2 and type:

This should trigger the Excel function editor, and it will prompt you to fill in some parameters for this function:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The following is a quick guide to how to fill in these values:

▪ Lookup_value. This is the value in our PRF record that we want to find in buildings sheet. This value identifies the PRF’s parent record (the record to which it belongs). In this case, the values we’re interested in are stored in the Building parent ID (ext) column in our PRF sheet. Note that there are just 3 unique values here, each of which relates to one of the 3 building records in the buildings sheet.

Set the value in the VLOOKUP to the appropriate cell reference which is ‘$O2′ in our example. The number will change for each row but we want to anchor this reference to column O (the $ symbol anchors the column value, so it will not be affected when we copy the formula into other cells).

▪ Table_array: is a reference to the range of data we want to reference and look up to add to our PRF sheet. To make this easier to reference, we will define our range and give it a name. In the buildings sheet, select all the records and then edit the label above the sheet and enter ‘buildings’, e.g:

We can then enter our table array here as ‘buildings’ rather than having to enter a more complicated range of values.

▪ Col_index_num: Again, to make this easier we will make use of a function in Excel to pull in column values automatically. This gets around the usual issue with Vlookup where you need to manually update column references when you copy the formula across columns. Enter column( and then on the buildings sheet, click in the cell for the first value for Building Name. This will automatically populate the value in the column function.

▪ Range_lookup: This defines whether we are happy for Vlookup to return a close match or if it should only ever return exact matches. Since we want to find an exact match for our building ID, set this to FALSE and close the function with a final parenthesis ‘)’.

Our VLOOKUP definition should now be complete, and should look like:

• =VLOOKUP($O2,buildings, COLUMN('preliminary-roost-building-info'!K2),FALSE)

If you’ve set up your spreadsheet slightly different, just adapt the formula above to take your situation into account.

You can drag-copy (click on the small green square in the corner, and pull down) this function into all the other cells in column 1 to populate the building name for each row:

and then drag-copy it across each column to populate all the remaining columns with the appropriate values from the buildings sheet:

Your PRF sheet should now contain combined data for both your buildings and PRF records.

This is a simple example, but it’s easily scalable to combine as many columns and rows as you wish from your data.

Shortcuts

You can use Excel shortcuts to quickly fill columns with formulae to save having to copy and paste into each cell.