5 Ways to Copy Unique Values to Another Worksheet in Excel

Sometimes we get datasheets with many duplicate values and we have to extract the unique values removing the duplicates. In this article, we will demonstrate five different methods to excel copy unique values to another worksheet.


📁 Download Excel File


Learn to Copy Unique Values to Another Worksheet in Excel with These 5 Approaches

Here in this dataset, we have a list of Student IDs. Here we can see many student IDs have been repeated. Now here we will only copy the unique values from this worksheet to another worksheet.

Dataset of excel copy unique values to another worksheet


Approach

 1. Applying Advance Filter Option

In this approach, we will utilize the Advance Filtering method to copy unique values from one worksheet to another. We will copy the unique student IDs to the worksheet under “Advance Filtering” here.

Using advance filtering to excel copy unique values to another worksheet.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Select cell B6.
  • From here we will copy the unique values.
  • After that, From the excel ribbon click the Data tab.
  • From the Sort and Filter section click the Advanced option.

  • After clicking the advance you will get the following window. Select Copy to another location.

  • Now click the Unique Records only and select the arrow sign in the List range.

Using Advance filtering excel copy unique values to another worksheet-2

  • After clicking the arrow sign you will get the following screen and then click the Dataset.

  • Select the range of cells B6 to B17.
  • After Selecting the cells and click Enter.

  • Now click OK.

  • After clicking the OK, we will get our desired Unique values in another sheet.

Here we can see we got all the unique student ids from the dataset sheet.

📕 Read More: 3 Easy Ways to Copy and Paste from Excel to Word Without Cells


Approach

2. Using UNIQUE Function

Now we will use the UNIQUE function to excel copy unique values to another worksheet. Here we will copy the Unique Student IDs from the dataset sheet to the UNIQUE function sheet. The UNIQUE function takes the values in a list of ranges and returns the unique values of that list or range.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Select cell B6.
  • Now type the function UNIQUE().
  • To provide the list or Range click the Dataset worksheet.

Using UNIQUE function to excel copy unique files to another worksheet

  • Now select the cells B6 to B17 in the Dataset.
  • Then click Enter key.
=UNIQUE(Dataset!B6:B17)

  • And after that, you will get the list of unique values in the UNIQUE Function.

Utilizing UNIQUE function to excel copy unique values to another worksheet

Here we can see using the UNIQUE Function we got the list of unique Student IDs. You can use SORT and UNIQUE Functions together to get a list of unique values in ascending order.

📕 Read More: How to Copy Same Value in Multiple Cells in Excel


Approach

3. Utilizing INDEX-MATCH Formula

In this method, we will use the INDEX–MATCH Formula to copy the unique values to another sheet. Here will provide a list to the INDEX MATCH and COUNTIF functions and it will return us the unique values from that list.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell no B6.

Using INDEX MATCH formula to excel copy unique values to another worksheet

  • Enter the following formula and press Enter key.
=INDEX(Dataset!B6:B17,MATCH(0,COUNTIF($B$5:B5,Dataset!B6:B17),0))

  • Now select cell no B6 and pull down the Fill Handle icon from B6 to B12.

Utilizing INDEX MATCH formula to excel copy unique values to another worksheet

And here we can see that we copied the unique Student IDs from the list of Student IDs in the Dataset Sheet to the INDEX MATCH worksheet.

🔨 Formula Breakdown

INDEX(Dataset!B6:B17,MATCH(0,COUNTIF($B$5:B5,Dataset!B6:B17),0))

👉  The INDEX function takes at least two arguments, the array and row_number or column_number, the array returns an array of entire rows and columns in an array. Here Dataset!B6:B17 is the input array.

👉  For the second input argument we used the MATCH and COUNTIF functions together.

👉  MATCH Function here returns the Student IDs that do not appear in the Unique Student IDs list.

👉  The COUNTIF function used to find out the duplicates. The COUNTIF function takes two inputs. What do we want to find and from where do we want to find? Here we used $B$5:B5 as an input argument to find out the unique values. Dataset!B6:B1 is used as the list from where we want to find out how many times these unique values appeared in the main list.

📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel


Approach

4. Use of LOOKUP Function

In this method, we are going to use the LOOKUP function to copy the unique values from the list of Student IDs From the Dataset worksheet to the LOOKUP worksheet.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select cell B6.

Using LOOKUP function to excel copy unique values to another worksheet.

  • Enter the following formula and press the Enter Key.
=LOOKUP(2,1/(COUNTIF($B$5:B5,Dataset!$B$6:$B$17)=0),Dataset!$B$6:$B$17)

Utilizing LOOKUP function to excel copy unique values to another worksheet.

  • Now select the B6 cell and drag the Fill Handle Icon from cell B6 to B12.

Also, we can see that by using that formula we extracted the Unique Student IDs, from the Dataset worksheet.

🔨 Formula Breakdown

👉  The LOOKUP function takes three arguments, The lookup_value, lookup_vector and the result_vector. Here in this article, the lookup_value is 2 which means the function will search for the largest value in the lookup_vector which is less than or equal to 2.

👉  This “1/(COUNTIF($B$5:B5,Dataset!$B$6:$B$17)=0)” quoted part of the formula represents a lookup_vector which returns a logical vector and the maximum value of the members of the vector is 1.

👉  Dataset!$B$6:$B$17 this represents the result_vector.


Approach

5. Executing VBA Code

In this approach, we will write a VBA Code to copy the Unique Values to another worksheet. Here we will copy the Unique Student IDs from the Dataset Worksheet to the VBA worksheet.

Using VBA code to excel copy unique values to another worksheet.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, click on the Dataset worksheet. Right-click on the Name Tab. Then you will see a window like below. Then click on the View Code.

  • After that, copy and paste the formula given below in the Visual Basic Window and click Run.
Sub Copy_Unique_Value()
Dim XSheet1 As Worksheet
Dim RngX As Range
Dim XSheet2 As Worksheet
Set XSheet1 = Worksheets("Dataset")
Set RngX = XSheet1.Range("B6:B" & XSheet1.Range("B65536").End(xlUp).Row)
Set XSheet2 = Worksheets("VBA")
RngX.Cells(1, 1).Copy XSheet2.Cells(5, 2)
RngX.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=XSheet2.Range("B5"), Unique:=True
End Sub

Utilizing VBA code to excel copy unique values to another worksheet.

  • Then go to the VBA Worksheet and see the result.

And we can see that it copied all the Unique Student IDs from the Dataset Worksheet to the VBA Worksheet.


📝 Takeaways from This Article

📌  Here we tried to demonstrate 5 different methods to copy unique values to another worksheet in excel.

📌  In The first method, we used excel advanced filtering to copy unique values to another worksheet.

📌  We also used different functions in this article. Such as UNIQUE, INDEX, MATCH, and COUNTIF to excel copy unique values to another worksheet.

📌  Finally, we used a VBA code.


Conclusion

In this article, we tried to show every possible way to copy unique values to another worksheet so you may the suitable method for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit Excelden to discover more about Excel.


Related Articles

(Visited 35 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo