Copying a column and pasting only the unique records in Excel is useful for data analysis, data validation, data entry, and data cleaning. It allows you to filter out unnecessary data, identify and remove duplicates, ensure the accuracy of the data, and avoid entering duplicate data. This post provides two methods to paste only the unique values from a source column in Excel.
Read More: Copy and Paste to Skip the Duplicate Values in Excel
Copy a Column and Paste Only the Unique Values by Advanced Filter Feature
Advanced filter is a powerful feature in Microsoft Excel that allows you to filter and extract data based on specific criteria. It provides more advanced filtering options than the regular Filter feature. It can extract unique values from a list and paste to another location.
You can follow the steps below to copy a column and paste only the unique records using the Advanced Filter:
Step1: Select the column that you want to copy.
Step2: Click on the Data tab in the top menu.
Step3: Click on the “Advanced” button in the “Sort & Filter” section.
Step4: In the “Advanced Filter” dialog box:
a. Check on the “Copy to another location” option.
b. In the “Copy to” field, select the cell or range where you want to paste the unique records.
c. Check the “Unique records only” checkbox.
Step5: Click the “OK” button in the “Advanced Filter” dialog box.
Excel will then copy the selected column to the specified location, and only the unique records will be pasted. Duplicate values will be excluded. In this example, since the first number “3” is listed in the starting cell of column C, it is considered to be the header of the list.
1. Make sure that the cell range you select for the copy destination is outside the range of the original data, otherwise, you may overwrite your original data.
2. The duplicate number in the first row in the column cannot be removed because it is used as a column header.
Copy a Column and Paste Only the Unique Values by Remove Duplicates Feature
Alternatively, you can use the “Remove Duplicates” feature in Excel to remove the duplicate records from a column. Before deleting duplicate records, copy the source column and then delete the duplicate records in the new column, which protects the source column from deleted the duplicate records and also copies only unique values from the new column to other locations.
Here are the steps:
Step1: Make a copy of the column you want to remove the duplicates.
Step2: Select the copied column that you want to remove duplicates from.
Step3: Go to the “Data” tab in the Excel ribbon.
Step4: Click on “Remove Duplicates“.
Step5: In the “Remove Duplicates” dialog box, uncheck “My list has headers” option, make sure that the column that you want to remove duplicates from is selected.
Step6: Click on “OK” to remove the duplicates.
Step7: An alert pops up to indicate you how many duplicate values found and removed, and how many unique values remain in the new column.
If you only want to copy the unique records elsewhere, you can copy column C to where you want to paste the unique records.