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 Check IF two values match (or differ)

Sometimes, in Excel, you need to see if two columns of data have matching (or different) values.

This can be easily accomplished by using the IF() function.

The IF() function takes 3 arguments (or pieces of info):

1. The Logical Test
2. The value to return if logical test returns TRUE
3. The value to return if logical test returns FALSE

In plain English it would look like this: "IF the data in field A2 is equal to the data in B2 then print the word 'MATCH' otherwise print the word 'Doesn't match'".

Now let's take a look at how to accomplish this in Excel.

• Open your spreadsheet and add a column with a header.

• Click the small arrow that is next to the auto sum button .

• Select More Functions....

• In the resulting pop-up type IF into the Search for a function box and click GO.

• Select IF in the Select a Function list and click OK

• In the Logical_test field type in your comparison.  For example you can check to see if the value in cell A2 match the value in B2 by clicking in the Logical_test field in the pop_up, clicking in field A2, typing an equal sign (=) and then clicking in field B2.

• Directly to the right of the Logical_test field you should see if your results return true or false.

• In the Value_if_True box type what you would like returned if the logical_test returns true.  Example: "Yes", "True", "They Match"

• Do the same for the Value_if_false field. Example: "No", "False", "uh oh".

• Again, you should see what the values will look like for each choice on the right hand side as well as the value for the row you have built your formula in, on the bottom.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
• Click OK.
  
• To expand the Formula to all your rows you can click the field that contains your formula, hover over it with the mouse in the very left, bottom corner of the field and click and drag the small square(your cursor will turn in to a small solid black cross).  You can also double click this small square to have it fill all the rows in your spread sheet for you.
  
• Have a beer you just saved yourself some time from having to look at every row in your sheet!
  
  
   

A few additional notes:

• You don't need to limit yourself to the equal operator in the Logical_test.  You can use <, > or < > (does not equal).  
• You don't need to limit yourself to text in the Value if True/False fields.  You can return a value from another cell by clicking the cell or you can insert another formula here as well.