Friday, October 21, 2016

Aligning two sets of data in excel with each other

Here is how to align two sets of data in excel each set containing multiple rows, but only one common element between the two to use as a 'key'.

In this example the BLUE and RED need to be aligned, the common element being the 4 digit number.

Example 1

With the code further below, the data will look something like this when merged, the red data now aligned with the blue data.



The data circled above in RED, ( C,D & E ) is now aligned with column A & B in example 2, with all the rows data maintaining their relevant position as well.

Here's some Excel magic.

  1. First make sure the common data of the two sheets are in the proper columns.  In the BLUE data, which contains the majority of the values, i put the 4 digit number on the right.
    The RED data, which is the lesser data for this example, I put on the LEFT
  2. First selected the the data that you want to align.  (the stuff in RED in my example) and move it to a new tab in the Excel document.

  3. In the new tab you created, make sure the data is selected
  4. In the name box (circled in red below) type in "MyData" in that box.  This gives that selected data a name now.



Now go back to the original tab that contains the data circled in Blue.
Were now going to have Excel look at 4 digit in column B (circled in BLUE) and pull data from "MyData" and merge it.

So, click on an empty column.  I've clicked on column "C" in my example.  Since I have columns to match, I'm going to do the code in 3 different columns

Assuming that in the blue data, the data is in the second column we would use the following code.
If it were in the first column, you would use $a1 and if it was in the 3rd you would use $a3
In Cell C1 put in:
=vlookup($b1,MyData,1,false)

Cell D1
=vlookup($b1,MyData,2,false)

Cell E1
vlookup($b1,MyData,3,false)

Like this:


Depending on the matches, you may see data appear or see #N/A, which indicates no match.


Selected the 3 boxes, then auto filled the boxes below with the formula.

All the data is aligned now.  The boxes that have no matches show "#N/A" in excel.

No comments:

Post a Comment