How to Copy Unique Values to Another Worksheet in Excel (5 Methods)

This article illustrates 5 different methods to copy unique values to another worksheet in the same workbook in Excel. Working with a dataset with duplicate values, we may need to get only the unique values to make a report or summary in a new worksheet. The methods described in this article are going to help you to extract the unique values and copy them to another worksheet.

How to Copy Unique Values to Another Worksheet in Excel (5 Easy Ways)

Let’s say we have a list of colors in Sheet1 that includes duplicates. We want to copy only the unique colors to another worksheet of the same workbook.

1. Copy Unique Values to Another Worksheet Using Advanced Filtering Option in Excel

Using the Advanced filtering feature of Excel, we will get unique values and copy unique values to another worksheet. Here we’re going to copy the unique colors in Sheet2.

Follow the guide below to accomplish our goal.

Steps:

• Select cell B4 from Sheet2, where we want to copy the unique values.
• Now, go to the Data tab from the Excel Ribbon.
• From the Sort & Filter section click Advanced.

• The above steps will open the Advanced Filter window.
• Check the “Copy to another location” and “Unique records only”

• After that, click the arrow sign to select the List range.
• Clicking the arrow sign will open an input box. Now, click on Sheet1 from the bottom of the worksheet.

• It’ll take us to Sheet1 where we have the color list. Using the mouse drag and select cells B5:B14. In the input box, we see the selected List range as “Sheet1!$B$5:$B$14”. Now, press Enter to continue.

• After hitting Enter key the Advanced Filter window will appear again. Click the OK

• Finally, we have the unique list of colors copied to Sheet2.
  

2. Use of the UNIQUE Function to Copy Unique Values to Another Worksheet in Excel

The UNIQUE function returns a list of unique values from a list or range of values. In this example, we’ll use the UNIQUE function to copy the list of unique colors to Sheet3 from Sheet1. Follow the simple steps.

Steps:

• Select cell B5 in Sheet3 and type the UNIQUE function.
• To put the array argument of the function, click on Sheet1.

• Now select cells B5:B14 in Sheet1 which contain the color list.

• Finally, close the parenthesis and press Enter.

3. Apply INDEX MATCH Array Formula to Copy Unique Values to Another Worksheet in Excel

In this example, we’ll use an array function based on the INDEX, MATCH, and COUNTIF functions to copy the unique values from Sheet1 to Sheet4. Here is the formula in general,
=INDEX(mainList, MATCH(0, COUNTIF(uniqueList,mainList), 0))

Do the following to complete the process to copy unique values from Sheet1 to Sheet4.

• In cell B5 of Sheet4, put the following formula and press Ctrl + Shift + Enter.

• Now locate the Fill Handle at the bottom right corner of cell B5 and drag it down until it returns an error.

• Here is the final output.

Explanation

The INDEX function needs two arguments – array and row_num. It’ll return a value to add to the unique list.
array – This argument is the list of colors in Sheet1 i.e., Sheet1!$B$5:$B$14.

row_num– We need to use the combination of the MATCH and COUNTIF functions to configure this argument. The MATCH function is used to find out the colors that do not appear in the unique color list. It eventually returns the first match if there are duplicates.

The COUNTIF function finds how many times a color in the unique list in Sheet4 appears in the main color list in Sheet1. Here we used $B$4:B4 as the expanding reference to look for colors in the unique list in Sheet4 and the main list as Sheet1!$B$5:$B$14 in Sheet1 from where to look for colors.

4. Use of the LOOKUP Function in Excel to Copy Unique Values

We can also use a non-array function using the LOOKUP function to copy unique values to another worksheet. This is the generic formula-

=LOOKUP(2,1/(COUNTIF(uniqueList,mainList)=0), mainList)

In this example, we need to put the color list in Sheet1 as the mainList and an expanding reference $B$4:B4 in Sheet5 as the uniqueList to copy the unique color list from Sheet1 to Sheet5. As a result, the formula turns into-

Put the above formula in cell B5 in Sheet5.

Using Fill Handle, we can copy and paste the formula quickly to get the full unique color list.

5. Copy Unique Values in Another Worksheet Using VBA Code in Excel

In this illustration, we’re going to use VBA code that will copy unique values from Sheet1 to a new worksheet named Sheet6. Let’s follow the simple steps to make it work.

Steps:

• Go to Sheet1 and right-click on the name tab.
• Then choose the View Code.

• Copy and paste the following code to the visual code editor and press F5 to Run.

Sub UniqueValue()
Dim WSheet1 As Worksheet
Dim Rng As Range
Dim WSheet2 As Worksheet
Set WSheet1 = Worksheets("Sheet1")
Set Rng = WSheet1.Range("B5:B" & WSheet1.Range("B65536").End(xlUp).Row)
Set WSheet2 = Worksheets("Sheet6")
Rng.Cells(1, 1).Copy WSheet2.Cells(5, 2)
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=WSheet2.Range("B5"), Unique:=True
End Sub

• Now navigate to Sheet6 and here is the output.

Code Explanation

In line 1, as pointed out in the above screenshot, we set the range as B5 to the end of column B from Sheet1 as we have our color list in cells B5:B14 in Sheet1.
Then in line 2, we set our targeted worksheet to copy the unique values as Sheet6.
Line 3 shows, we want to copy the first unique value to cell(5,2) i.e., B5 in Sheet6.
Again, in line 4, we set the filtering criteria and enabled Unique to True.

Notes

• If we want to avoid the #N/A error while applying method 2 and method 3, we can use the IFERROR function with it. The formula becomes-

• We need to press Ctrl + Shift + Enter to insert an array formula.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Now, we know how to copy unique values to another worksheet using 5 different methods. Hopefully, it will help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below

<< Go Back to Unique Values | Learn Excel