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.


Download the Practice Workbook

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


5 Methods to Copy Unique Values to Another Worksheet in Excel

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.

Copy Unique Values to Another Worksheet in Excel


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

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

Copy Unique Values to Another Worksheet in Excel

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 the Advanced

Copy Unique Values to Another Worksheet in Excel

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

Copy Unique Values to Another Worksheet in Excel

  • 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.

Copy Unique Values to Another Worksheet in Excel

  • 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.

Copy Unique Values to Another Worksheet in Excel

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

Copy Unique Values to Another Worksheet in Excel

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

Read More: Excel Formula to Copy Cell Value from Another Sheet


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.

Copy Unique Values to Another Worksheet in Excel

  • Finally, close the parenthesis and press Enter.

Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria


Similar Readings


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.
=INDEX(Sheet1!$B$5:$B$14, MATCH(0, COUNTIF($B$4:B4,Sheet1!$B$5:$B$14), 0))

Copy Unique Values to Another Worksheet in Excel

  • 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.

Copy Unique Values to Another Worksheet in Excel

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_numWe need to use the combination of the MATCH and COUNTIF function 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.

And 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.

Read More: Excel Formula to Copy Text from One Cell to Another Sheet


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-

=LOOKUP(2,1/(COUNTIF($B$4:B4,Sheet1!$B$5:$B$14)=0),Sheet1!$B$5:$B$14)

Put the above formula in cell B5 in Sheet5.

Copy Unique Values to Another Worksheet in Excel

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

Read More: Macro to Copy and Paste from One Worksheet to Another (15 Methods)


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.

Copy Unique Values to Another Worksheet in Excel

Steps:

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

Copy Unique Values to Another Worksheet in Excel

  • 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.

Copy Unique Values to Another Worksheet in Excel

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.

Read More: Macro to Copy Specific Columns from One Worksheet to Another in Excel


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-
=IFERROR(INDEX(Sheet1!$B$5:$B$14, MATCH(0, COUNTIF($B$4:B4,Sheet1!$B$5:$B$14), 0)),"")

Copy Unique Values to Another Worksheet in Excel

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

Conclusion

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


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo