Excel tip- How do I add a column based on values in another column

I got this request today at work and thought I would share several fairly easy solutions.

The issue was that she had a spreadsheet that needed a new column based on data that was already in the spreadsheet.  In this example she had a column called region with a possible value of North, East, South or West.  She wanted to add in the manager responsible for these regions.  In this situation there are two managers and they split the regions, 2 each.

Solution 1:

• Copy the entire column, paste it into your new column and do a find and replace in the new column.  repeat this for all 4 regions.  Make sure you only have the one column selected so you don't replace the values from the original column which should still hold the region info.

Solution 2:

• Sort the region column and then in the first cell in your new column type the managers name.  Drag the value (using the heavy crosshair cursor you get when you hover over the bottom right corner of the cell) until you have filled all the values for that region and then repeat for the other 3 regions.

and Solution 4:  

• Use a formula! 

• In this example we will actually need two formulas (OR and IF).  Lets break them down one by one.

• Lets start with IF which will take three values one of which will be our OR function, more on that later.  First it will take some condition that it will test and see if it is true or false.  If it is true it takes the second value and if it is false it will take the third value.  In sentence format it would look like this:
  
  • If "this is true", "then do this", "otherwise do this".   
  • In our example it is going to be "If the region is 'South' OR the region is 'North' then use the name 'Smith', other wise use 'Jones'"
•  In Excel's syntax it will look more like this  =IF(logical test,"Smith","Jones").  
• Now lets look at the OR function which is needed since we have two conditions that might require putting in the same value.
• Or takes as many logical conditions as you want to test (not sure if there is a limit) and returns TRUE if there is one that is true and FALSE if they are all false.
• For our example we would do something like this =or(A1 = "South", A1 = "North").  This will simply return "True" if the value in A1 is either South or North which is perfect for our IF statement because as you remember, the first thing that it is looking for is a condition to test to see if it is true or false. 

Now lets put it all together:
=IF(OR(A1 = "South", A1 = "North"),"Smith","Jones")

Drag the formula to all the cells in the report (or double click the dark crosshair) and you are good to go!

Excel tip - How do I merge data from one sheet into another

So you have a spread  sheet with a large amount of data in it and you have done a fair amount of work manipulating the data when you realize that you are missing some info. 

You are able to get the missing info from another spreadsheet but don't want to have to start your data manipulation over again.  Well, you are in luck.

As long as you have a common unique field in both spreadsheets you will be able to use VLOOKUP() to merge your data.  Here is how:

VLOOKUP is an excel function that takes 4 (or optionally 3) arguments (or pieces of info that you give it).

The format will look like this: =vlookup(lookup_value, table_to_search, column_number_returned, exact_or_not)

Now let's break it down so it makes sense.

In this example will be taking baseball player names and teams from one sheet and adding the position of the player from a second sheet that has the players name and position.  The screen shots have a very small amount of data in it but if you could imagine having hundreds or thousands of players to compare how much time this will save you.


To start we have this data:

 

And in our second spread sheet we have this:



In the table you would like the new data merged into, add a column and give it a header row.  Put your cursor in the second row and click on the arrow next to the auto sum button and type vlookup in the search field.









Now in the pop-up that takes the function arguments put your cursor in the Lookup_value field and then place your cursor in the first cell in your spread sheet that you will try to match up.  In this example it will be in the sheet that has the names and teams of the players and I will put my cursor in cell A2 (where it says "Cabrera".)  Or you can type your cell number (A2).

You should see the value populated to the right of the lookup_value field.  Now you need to define where excel will look for this info.  In this example we can now place our cursor in the Table_array field and click over to our second sheet. Excel populates the sheet name for us.  We will now highlight ever row starting with the column that has our unique values and ending with the field we are going to try to add to the first sheet.  In this example we only have two columns so I will select both but you may have columns in between the two fields you will be using and if that is the case just select the ones in between as well.  You will do this by clicking on the letter in the top of the column and dragging over until you have highlighted all the columns you need.  In my example excel populates the Table_array field with "Sheet2!A:B".

Now you need to tell excel what column to return when it finds a match.  In this case it will be the 2nd column so I type a 2. If you have columns in between your search column and the column that contains the value you would like to return simply count the columns (starting with the search column and ending with the return column) and enter that number.  Example: If your search column is column D and you are returning values in column H your previous argument would say "Sheet2!D:H" and for this argument you will want to put a 5.

The last argument is optional and has a funny name of Range_lookup. This argument takes either the value "TRUE" or "FALSE" and determines if excel should look for an exact match or not.  FALSE will find an exact match and True (or leaving it blank) will find the closest match in the search column.  If you are working with numbers or currency you can use TRUE if you would like to return the closest match, but keep in mind you want to have your tables sorted by the value you are searching on because it will pull in the next value it finds and if they are out of order you returned values can be very wrong.  With text searches I would think that you would want an exact match, so use the FALSE value here.

So our finished pop-up window looks like this:



You can see that the value "Outfield" has been correctly found indicating that my formula is correct.  Keep in mind that this may return #N/A if the value you are searching for doesn't exist in your Table_array.

Click OK and you will see the value in your 1st sheet.  Click the box with your formula, hover over it and you will see a small black box in the bottom right hand corner of that cell. If you hover over that little black box your cursor will change to a denser, smaller cursor and you can then double click to populate all of your formulas.

Our final results look like this:



You can see that a few values were not found (I left them out on purpose) and those are returned as #N/A.

If you want to, you can now selected everything in the row, copy, and right click and select Paste Special.  Choose Values and click OK.  This will replace your formula with the values that your formula returned and it will be as if your data was there all along.

I hope you find this helpful.  I know I use it all the time and it is a real time saver.  The first few times I used vlookup it took some getting used to but I assure you that if you use it a few times it will be second nature. Good luck.