How to Get Unique Data from Specific Columns to Another Column in Excel

Introduction

Sometimes, you may have a dataset that contains duplicate values in one or more columns, and you want to extract only the unique values from those columns to a new column. For example, you may have a list of customers with their names, email addresses, and phone numbers, and you want to get a list of unique names or email addresses in a separate column.

There are different ways to achieve this task in Excel, depending on your version and preference. In this article, we will show you three methods to get unique data from specific columns to another column in Excel:

• Using the Remove Duplicates tool
• Using the Advanced Filter tool
• Using the UNIQUE function (Excel 365 and later versions)

We will also explain the basic theory behind each method, the steps to follow, and the advantages and disadvantages of each method. We will use a sample dataset to illustrate each method with real numbers and screenshots.

Sample Dataset

For this article, we will use the following sample dataset, which contains the names, email addresses, and phone numbers of some customers. Notice that there are some duplicate values in each column.

Method 1: Using the Remove Duplicates Tool

The Remove Duplicates tool is an in-built feature in Excel that allows you to quickly remove all the duplicate values from a range of cells and leave only the unique ones. This is the simplest, quickest, and most commonly used method to get unique data from specific columns to another column in Excel.

The Remove Duplicates tool works by comparing the values in each row or column of the selected range and deleting the rows or columns that have duplicate values. You can choose which columns to compare and which columns to keep or remove. The tool affects your original data, so it is a good idea to make a copy of your data before using it.

Steps

To use the Remove Duplicates tool to get unique data from specific columns to another column in Excel, follow these steps:

1. Select the range of cells containing your data. In our example, select the range A2:C11.
2. Click on the Data tab and then click on the Remove Duplicates button in the Data Tools group. This will open the Remove Duplicates dialog box.
3. In the Remove Duplicates dialog box, check the columns that you want to compare for duplicates. In our example, check the Name column and the Email column. This means that Excel will compare the values in these two columns and remove any rows that have duplicate values in both columns. You can also check the My data has headers option if your data has column headers.
4. Click OK. Excel will remove the duplicate rows and show you a message box informing you how many duplicate values were removed and how many unique values remain. Click OK to close the message box.
5. You will now see the unique values from the selected columns in the same range. You can copy and paste these values to a new column if you want. In our example, you will see the following result:

Advantages and Disadvantages

The Remove Duplicates tool has the following advantages and disadvantages:

• Advantages:
  • It is easy to use and fast to execute.
  • It works with any data type: text, numbers, dates, times, etc.
  • It allows you to choose which columns to compare and which columns to keep or remove.
• Disadvantages:
  • It affects your original data, so you need to make a copy of your data before using it.
  • It does not work with non-adjacent columns, so you need to rearrange your data if you want to compare columns that are not next to each other.
  • It does not allow you to specify any criteria or conditions for removing duplicates, such as case sensitivity, partial matches, etc.

Method 2: Using the Advanced Filter Tool

The Advanced Filter tool is another in-built feature in Excel that allows you to filter your data based on various criteria and display the results either in-place or in a new location. You can use this tool to get unique data from specific columns to another column in Excel by applying a filter that shows only the unique records.

The Advanced Filter tool works by applying a set of criteria to a range of cells and displaying the rows or columns that meet those criteria. You can specify the criteria in a separate range of cells, called the criteria range, or use the default criteria of showing all records. You can also choose whether to display the results in the same range as the original data, called the filter in place option, or in a different range of cells, called the copy to another location option. The tool does not affect your original data, unless you use the filter in place option.

Steps

To use the Advanced Filter tool to get unique data from specific columns to another column in Excel, follow these steps:

1. Select the range of cells containing your data. In our example, select the range A2:C11.
2. Click on the Data tab and then click on the Advanced button in the Sort & Filter group. This will open the Advanced Filter dialog box.
3. In the Advanced Filter dialog box, choose the Copy to another location option. This means that Excel will copy the filtered results to a new range of cells, without affecting your original data.
4. In the List range box, enter or select the range of cells containing your data. In our example, enter or select A2:C11.
5. In the Criteria range box, leave it blank. This means that Excel will use the default criteria of showing all records.
6. In the Copy to box, enter or select the range of cells where you want to display the results. Make sure that this range is in a different worksheet or workbook, or in a blank area of the same worksheet. In our example, enter or select E2:G2.
7. Check the Unique records only option. This means that Excel will only copy the rows that have unique values in the selected columns.
8. Click OK. Excel will copy the unique values from the selected columns to the specified range. You will see the following result in our example:

Advantages and Disadvantages

The Advanced Filter tool has the following advantages and disadvantages:

• Advantages:
  • It does not affect your original data, unless you use the filter in place option.
  • It works with any data type: text, numbers, dates, times, etc.
  • It allows you to specify any criteria or conditions for filtering your data, such as case sensitivity, partial matches, logical operators, etc.
  • It allows you to display the results in a different worksheet or workbook, or in a blank area of the same worksheet.
• Disadvantages:
  • It is more complex and time-consuming to use than the Remove Duplicates tool.
  • It does not work with non-adjacent columns, so you need to rearrange your data if you want to compare columns that are not next to each other.
  • It does not update automatically when your data changes, so you need to repeat the steps to refresh the results.

Method 3: Using the UNIQUE Function (Excel 365 and later versions)

The UNIQUE function is a new function in Excel 365 and later versions that allows you to return a list of unique values from a range or array. It works with any data type: text, numbers, dates, times, etc. It is a dynamic array function, which means that it returns a dynamic array that automatically spills into the neighboring cells vertically or horizontally.

The UNIQUE function works by comparing the values in each row or column of the selected range or array and returning the rows or columns that have unique values. You can choose whether to

compare the values by rows or by columns. You can also choose whether to ignore the case of the values or not. The function returns a dynamic array that automatically spills into the neighboring cells vertically or horizontally, depending on the shape of the input range or array.

Steps

To use the UNIQUE function to get unique data from specific columns to another column in Excel, follow these steps:

1. Enter or select the range of cells where you want to display the results. Make sure that this range is in a different worksheet or workbook, or in a blank area of the same worksheet. In our example, enter or select E2:G2.
2. In the formula bar, enter the UNIQUE function with the following arguments:
   • array: The range or array from which to return unique values. In our example, enter or select A2:C11.
   • by_col: Optional. A logical value that determines how to compare the values. If TRUE, compare by columns; if FALSE or omitted, compare by rows. In our example, enter FALSE, since we want to compare by rows.
   • exactly_once: Optional. A logical value that determines which values to return. If TRUE, return only values that appear exactly once in the array; if FALSE or omitted, return all unique values. In our example, enter FALSE, since we want to return all unique values.
3. Press Enter. Excel will return a dynamic array that contains the unique values from the selected columns. You will see the following result in our example:

Advantages and Disadvantages

The UNIQUE function has the following advantages and disadvantages:

• Advantages:
  • It does not affect your original data, unless you overwrite it with the function.
  • It works with any data type: text, numbers, dates, times, etc.
  • It allows you to choose whether to compare by rows or by columns, and whether to ignore the case of the values or not.
  • It returns a dynamic array that automatically spills into the neighboring cells vertically or horizontally, depending on the shape of the input range or array.
  • It updates automatically when your data changes, so you do not need to repeat the steps to refresh the results.
• Disadvantages:
  • It is only available in Excel 365 and later versions, so it may not work in older versions of Excel.
  • It does not allow you to specify any criteria or conditions for returning unique values, such as partial matches, logical operators, etc.
  • It may return an error if the output range is not large enough to accommodate the dynamic array, or if there are any non-empty cells in the spill range.