Understanding the Unique, Join and Transpose Functions in Google Spreadsheets

Manually modifying data in Google Spreadsheets can become time consuming, especially when you’re working with a cumbersome data set or elaborate spreadsheet. Before you find yourself wasting hours of your time re-typing or removing details manually in your spreadsheets, explore the Google Spreadsheets Function List and discover if there are formulas and functions to do the work for you.

Adding formulas and functions to Google Spreadsheets will save you time because you can automate various actions, such as deleting duplicate rows, joining values from various cells into one cell and altering the layout of your data from a horizontal view to a vertical view. To automate these specific actions in your Google Spreadsheets, check out the Unique, Join and Transpose functions.

In this post, we will review use cases, syntax and bonus tips for the following functions in Google Sheets:

• Unique Function
• Join Function
• Transpose Function

Unique Function

Before you proceed with manually deleting duplicate rows in a long list of data, check out the Unique function. The Unique function is categorized as a filter type of function, and allows you to discard duplicate values or rows in the source array.

This function is comparable to the Remove Duplicates feature in Microsoft Excel.

Use Case:

By accident, a collaborator duplicated the California branch employees on the Employee Contact List. We will use the Unique function in a formula to discard the duplicate entries, or Rows 3, 5, 7, 9, 11, 13 and 15, shown in blue font below.

The following are steps for using the Unique function in Google Spreadsheets:

1.  Choose an area of your spreadsheet where you would like to return only one instance of each of the duplicate rows in your source array. In the example, we have chosen to display the unique data beside the range of data displaying duplicates.

2.  Add a formula with the Unique function using the following syntax: =Unique(SourceArray)

2. a.  The source array can be added to the formula manually by typing the range of data, (A1:F22 in the example) or by simply highlighting the range of data after entering =unique( in the cell.

3.  Hit enter to automate removal of any duplicate rows from the source array. In the example, the end result is 15 rows of unique employee details (including the header row). Seven duplicate California branch employees and their corresponding contact details have been discarded and the unique rows are listed in the same order as they were displayed in the source array.

Tip: The new range of data is essentially a range of calculations of formulas using the Unique and Continue functions. Follow these steps to remove the formulas, yet keep the values intact:

1. Select and copy your range (H1:M15 in the example)

2. Select the destination where you would like to paste.

3. Click the Edit menu and Paste Special, then select the Paste values only option from the drop-down list.

Join Function

Before you proceed with typing new data in a cell to combine details that are currently split into two cells, check out the Join function in Google Spreadsheets. The Join function is categorized as a text type of function, and allows you to link values from several cells into one text string using the delimiter of your choice. A delimiter is the character(s) that separates the values you will be joining together in one cell using the join function in a formula. The delimiter you choose depends on your data set, so choose what makes sense to you, whether it is text, punctuation, spaces or a combination of these.

This function is comparable to the concatenate function in Microsoft Excel.

Use Case:

We would like to compress the Employee Contact List to include fewer columns, allowing us to print numerous copies of the list on one page and distribute hard copies to team members without wasting a lot of paper. We want to combine the employees’ First and Last names into one column, therefore we will use the Join function in a formula to combine text from Columns A and B into one string. In the example, we will use a space as the delimiter.

The following are steps for using the Join function in Google Spreadsheets:

1. Insert a new column or choose an area of your spreadsheet where you would like to return the combined text strings. In the example, we have chosen to insert a new column, (Column C) with the heading Full Name.

2.  Add a formula with the Join function using the following syntax: =Join(delim,array_1,...)
2. a.  Delim refers to delimiter. If your delimiter is an alphabetic character or a space, use quotation marks when adding it to the formula. In the example, there are quotation marks surrounding a space, which will separate the employee’s first and last name.

2. b.  The arrays can be added to the formula manually by typing the cell coordinates (A2 and B2 in the example). You also can select the first array, add a comma, and then select the next array you would like to combine with the first array selected. Taking this action will automatically populate the selected arrays into the formula, and the colored, dashed lines will display around the selected cells.

3.  Hit Enter to display your combined arrays. If you would like to autofill the formula down a column, select the cell and hover over the bottom, right corner until a cross displays.

4.  Next, double click when the cross is displayed and the formula will be copied down the column. In the example, the final result is a combined First and Last Name with a space separating the two values in Column C.

5.  If you prefer to remove the formulas from your new data, use paste special - paste values only (as described in the tip above) - to display only the values. After doing so, you can delete the unnecessary columns by highlighting them and then right clicking on one, and selecting Delete columns A - B from the drop-down. Alternatively, you may choose to leave the formulas in the cells and simply choose Hide Columns A - B (after selecting the columns and right clicking) from the drop down, to hide the unjoined data from view.

Tip: Use “, “ (a comma and a space) as a delimiter when you are using the join function to combine names in a Last Name, First Name or locations in a City, State format. For example, adding the formula =join(“, “,B2,A2) in Column C2 with the sample data above, will produce Elliott, Martha and allow for sorting by last name.

Transpose Function

Before you proceed with recreating existing data in a different layout, check out the Transpose function. The Transpose function is categorized as an array type of function, and allows you to transpose the rows and columns of a data set to change your layout from horizontal (headers at the top of each column) to vertical (headers at the beginning of each row), or vice versa. If you are looking for an easy way to switch the layout of your data, adding a formula with the Transpose function will get the job done in just a few clicks.

This function is comparable to the paste special-transpose operation in Microsoft Excel.

Use Case

We would like to change the Company Contact List layout from it’s current horizontal layout to a vertical layout. We want to view each of the employee’s contact details in rows and cells below their name, as opposed to in columns and cells next the their names. To automate the action in a different area on the spreadsheet, we will add a formula using the Transpose function.

The following are steps for using the Transpose function in Google Spreadsheets:

1.  Select your target area where you would like to return your transposed rows/columns. In the example, we have chosen to display our transposed rows and columns beginning in cell A17, and below the current Employee Contact List.

2.  Add a formula with the Transpose function using the following syntax: =Transpose(array)

In the example, the array is A1:D15.

3.  Hit enter to transpose the rows and columns of your array. Note: To keep screen shot size to a minimum, the example below does not show the entire transposed array.

Whether the task at hand is removing duplicate rows within a range, combining values from multiple cells into one cell, or transposing the rows and columns of a data set, adding a formula to manipulate the data is much more efficient than removing or retyping existing data by hand. To further increase productivity, share your Google Spreadsheets with others and collaborate in real time on revisions.  If you have any questions about the functions in Google Spreadsheets, we invite you to chat with one of our experts during Blog Office Hours, every Thursday from 11-12 EST. Additionally, if you would like to schedule Google Apps training for your organization, please visit the Training section of our website.