Combining exported data in Excel

In this guide you will learn how to reconcile data from related forms exported from Coreo by using Microsoft Excel.

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 records.

Export your data from Coreo

For this example, download the sample data but you can always export your own data from Coreo go try (see Exporting records for a guide on how to do this).

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

  • export-example-site-3.csv
    • contains our site records, including geospatial data
  • export-example-species-records.csv
    • contains our species records as a sub-form of the site form

Import your data into Excel

Open Microsoft Excel, and create two empty sheets.

Import each file into a separate sheet – import Sites records to Sheet1, Species records to Sheet 2. To import, use File > Import and choose CSV file, select delimited file, and use the comma delimiter.

You should now have both sheets populated with the records from each import file.

Combine the records

Select Sheet2 – the sheet into which you have imported the species records. You should have columns for lat, lng and geometry (columns J,K and L) and these should all have empty cells in the record rows. We are going to populate these values from the other sheet. We will use the vlookup function to do this.

Place your cursor in cell J2 and type:

=vlookup(

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)

 Lookup_value: is the value we want to match against in order to lookup data from the Sites sheet. In this case, it’s the ID of the parent Site form (1910418), which is stored in the species_subform column in our Species sheet. So here we will set this to ‘$Q2‘ as this will change for each row but we want to anchor it to column Q (the $ symbol anchors the values, so they will not change when pasted into different cells).

 Table_array: is a reference to the range of data we want to look up and add to our Species sheet. To save time, we will set this to cover both rows in the Sites sheet including the data we’re interested in, so this should be: Sheet1!$A$2:$L$3 (where Sheet1 is our Sites sheet)

 Col_index_num: is the column number in table_array from which the matching value should be returned. The ‘lat’ column is 10, ‘lng’ is 11, and ‘geometry’ 12.

 Range_lookup: we want to find an exact match, so set this to FALSE.

J2 should read:

  • =VLOOKUP($Q2,Sheet1!$A$2:$L$3,10,FALSE)

We can copy this function into cells J3, J4 and J5 to link the ‘lng’ values across for all the species records. Make sure the lookup_value is incrementing according to the row (so it should read $Q3, $Q4, and $Q5).

Now repeat this by pasting the function into K2, K3, K4 and K5 but update the column_index_num value from ’10’ to ’11’ to pick up the ‘lat’ value.

Finally, repeat the function paste again for L2, L3, L4 and L5 and update the column_index_num value from ’11’ to ’12’ to pick up the ‘geometry’ value.

You should now have populated cells for lat, lng and geometry in the Species sheet with the associated values from the Sites sheet.

excel-complete

The important takeaway is identifying the cell(s) that reference the ID of the parent form, which then allows you to do lookups against that data to use the data in other sheets or reports.

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

Was this article helpful?
YesNo