How to automate a ‘mapping’ procedure

Overview

Commonly, there are situations where cell values which belong to a particular category must be matched or ‘mapped’ to cell values of a corresponding category. An example of such a situation may be where client names need to be matched to geographical regions. Manually, such a situation may be very time consuming and prone to human error. To ‘map’ just a single client name, a person would first need to determine whether that name exists in a reference table (a table of client names and geographical regions, that is, a ‘map’). Then, that person would need to search for and find that name in the reference table to discover the region to which it belongs. SolveXia can be used to complete such ‘mapping’ procedures in a fraction of the time required to complete them manually.

For the purposes of this article we are going to use three simple and fictitious Excel files. They have already been uploaded into a data step. You will want to replace these files with those of your own.

Before we get started

Please further create a ‘Manipulate a file’ or ‘Multiple file manipulations’ action step so that you can add and use instructions.

Let’s suppose that you have two files. One file contains a list of names which need to be ‘mapped’:

The other file contains the ‘map’:

The cleanest way to perform this ‘mapping’ procedure would be to 1) create an Excel workbook with 2 blank worksheets (we can refer to this as the ‘interim file’) , 2) copy the list of names to one of the worksheets, 3) copy the ‘map’ to the other worksheet, 4) in the first worksheet, use formulas to return the appropriate region names in the column next to the list of names.

The interim file resembles the image below:

Steps

1. Create a copy cell values instruction to copy the list of names from the ‘Names’ file to Column A of the ‘Names’ worksheet of the interim file.

1. Create a copy cell values instruction to copy the ‘map’ from the ‘Map’ file to the ‘Map’ worksheet of the interim file.

3. Create an update cell formula instruction to update “=VLOOKUP(A2,Map!A:B,2,FALSE)” into Column B of the ‘Names’ worksheet of the interim file.

4. Save and run the step.

5. After the step finishes running, download and open the target file. You will see that in the ‘Names’ worksheet, the list of names which were copied into Column A have been ‘mapped’ to the appropriate regions:

Leave a Reply

Latest Tweets

Free eBook: 10 Tips for sourcing good data yucalc.com/2017/02/17/fre…

reply · retweet · favorite

Newsletter

By signing up, you agree to our Terms of Service and Privacy Policy.

Address

Suite 1506, Westfield Tower 2, 101 Grafton St Bondi Junction NSW 2022 Australia

View Larger Map

Contact Us

AUS: +61-2-9386-0202
UK: +44-20-8123-1251
Send us an email

ContactUs.com
WordPress Image Lightbox