Importing and Merging Datasets

Datasets come in a variety of formats – from MS Access, from SPSS, or from CSV files, for example. These tutorials will help you import, export, and merge datasets so you can ultimately start analyzing the data in Excel.

Tutorials about importing, exporting, and merging datasets include:

Or, watch the entire importing and merging playlist in YouTube.

How to parse data using the ‘text to columns’ feature

Sometimes we receive evaluation data in CSV format. CSV files contain “comma separated values,” which means your numbers and text are stored in one big text file with a comma between each value. However, in Excel, we need each value in its own column. In this video, I show you how to transform your CSV data into a tabular format for Excel within a minute using the “text to columns” icon. To learn more about CSV files, check out the Wikipedia definition here.

When to use vlookup

The =vlookup() function is my all-time favorite function in Excel because it helps us merge data from various columns, sheets, and files into one spreadsheet. However, vlookup is a lesser-known feature of Excel, typically used only by advanced users. Vlookup is needed so often and saves so much time that I’ve dedicated multiple videos to explaining this function. I hope you enjoy learning about one of Excel’s best functions.

Why to use vlookup

Why should you bother learning about the =vlookup() function? Vlookup saves you time as you’re merging datasets together. In this video, I demonstrate how it’s painstakingly slow to copy and paste data from different spreadsheets together when one spreadsheet contains 430 rows of data and another spreadsheet contains 137 rows of data.

How to use vlookup

In this tutorial, I walk you through the four sections of the vlookup function.

Here’s what the function looks like: =vlookup(lookup_value,table_array,col_index_num,[range_lookup])

And here’s what each piece of the function really means:

  • lookup_value: The cell that contains the person’s ID number. The ID numbers are the link or key that connect all the spreadsheets together. The ID numbers must be located in the first column of each table – in the first column of your new combined dataset and in the first column of every single table from which you’re pulling data.
  • table_array: This is the table from some other spreadsheet or other file from which you’re pulling data.
  • col_index_num: This is the column in that table where you’re pulling the data from. Just type in the number of the column. For example, if you want to pull in data from Column C of another table, you’d type “3.”
  • range_lookup: Always type “false” and you’ll be in good shape.

Vlookup time saver #1

Vlookup time saver #2


Return to top

Jump to: Excel for Evaluation homepage | Importing and Merging | Housekeeping | Cleaning and Recoding | Exploring | Descriptives | Pivot Tables | Charts and Graphs | Extras | Putting It All Together

2 thoughts on “Importing and Merging Datasets

  1. Elio

    Ann thanks so much..!!! very helpful. I’m working in Mozambique and there’s no way to get updates and tips here for using Excel in evalution. Any of such videos on SPSS? So your help is very important for my work. Thanks again. Elio

    Reply
  2. sherry

    I wonder if you could do one more quick video on how you came up with the ids in column A? If you take your original 2 datasets and copy, paste and sort, you end up with duplicate I’d numbers, of course. Thanks! Great videos!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s