Tuesday, June 21, 2011

Combine two columns of data into one

I'm going to start off this blog with a fairly obscure function. Not obscure because the formula is rare, but because I don't imagine that many people would be needing to do this. However, since I've come across the need to do this on more than one occasion, there may be other "-ologists" who wish to learn.

The Need:
I first used this formula when I had a large bird point count dataset with many plots in different management units (or habitats).  I wanted to combine the Unit Names with the Plot Numbers, which were in two separate columns, into a single column.
 For Example, the original data looked like this:

I wanted to have each plot-habitat have its each unique name.

Formula Example:
To combine two columns into one (in my example columns A and B with a dash between), use the following formula (typed into Column C):

=A2 & "-" & B2

Another example of my use of this formula was to create a plot name from UTM Easting and Northing GIS points.  This was for a pre-treatment (prescribed burn) range vegetation survey and we decided to use the utm coordinates as the plot name so that the plot name wouldn't get dis-associated with the plot location in the future.  Photo points were also named with the utm coordinates in this project. The following example shows the use of the combine columns formula to combine UTM E (column B) with UTM N (column C), again with a "-" bewteen them:

- You can change the symbol that separates them by using any letter, just subsitute the "-" mark with any other symbol, such as an "&", etc.
- I used relative references in my example, so could copy and paste the formula into all rows.
- To use this datasheet for further analysis, I would copy the newly-made column with new names and paste it as values.  This will eliminate the formula and replace it with simple text.

Examples of this and other natural-resource related Excel tools can be found by clicking here.

No comments:

Post a Comment