Copying and pasting values while skipping duplicates in Excel can be useful in many situations where you want to analyze or manipulate data without duplicates. Duplicates can create inconsistencies and errors in calculations, charts, and other data processing tasks. By copying only the unique values, you can create a clean and consistent data set that is easier to work with and less prone to errors. This post provides two ways to copy and paste to skip duplicates in a column in Excel worksheet.
Read More: Copy a Column and Paste Only the Unique Values in Excel
See the example below. Suppose we enter an extra rate value “1.78” in the list. We want to remove the duplicate when copying and pasting the column to another location.
Copy and Paste to Skip the Duplicate Values by Advanced Filter Feature
You can use Advanced Filter to copy and paste the unique values in a list while skipping duplicates in Excel.
Here are the steps:
Step1: Select the range of cells containing the values you want to copy.
Step2: Go to the “Data” tab and click on “Advanced” in the ribbon.
Step3: In the “Advanced Filter” dialog box:
a. Select “Copy to another location” and
b. Specify the range where you want to paste the unique values.
c. Check the box next to “Unique records only“.
d. Click OK.
This list is copied to the location you specified properly with no duplicates.
This method will copy and paste only the unique values from the selected range to the specified location, skipping any duplicates.
Copy and Paste to Skip the Duplicate Values by Excel INDEX & MATCH Formula
You can also use the INDEX function with other functions such as MATCH and COUNTIF to copy and paste unique values while skipping duplicates in Excel. The combination of INDEX and MATCH functions in Excel is a powerful way to look up a value in a table based on specific criteria.
Here’s the generic formula:
=IFERROR(INDEX(rng,MATCH(0,COUNTIF($A$1:A1,rng),0)),"")
Here, “rng” refers to the range of cells containing the values you want to copy, and “$A$1:A1” refers to the cells above the current cell in the column where you want to paste the unique values, you can assume that “$A$1” is used to hold the list header. You can adjust these references as needed for your specific data.
In this example, the formula is:
=IFERROR(INDEX($B$3:$B$10,MATCH(0,COUNTIF($D$2:D2,$B$3:$B$10),0)),"")
Here are the steps to build and use this formula:
Step1: Select the cell where you want to start the list of unique values. In this example, select the ell D3.
Step2: Enter the formula in the formula bar or in cell D3.
Step3: Press Ctrl + Shift + Enter to create an array formula.
Step4: Copy the formula down to the cells below as needed. This will create a list of unique values in the specified column, skipping any duplicates. It ends when an empty cell is returned.
Step5: You can adjust the formatting by copy the format from the source column and paste only the formatting via Paste Special -> Formatting.
Excel IFERROR function is used to handle errors by returning a specified value if a formula evaluates to an error, and the original result if the formula evaluates to a valid value. In this case, if IFERROR function returns an empty cell when formula value is wrong.
If you remove the IFERROR function statement in this formula, the formula created by the Excel INDEX function and MATCH function is still valid, the new formula can create a list of only unique values, but when the end of no value returned, the cell shows an error #N/A, which can stop copying the formula.
