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

## 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. 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. ⬇️⬇️ 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. • 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.

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

Approach

### 3. Utilizing INDEX-MATCH Formula

In this method, we will use the INDEXMATCH 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. • 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. 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.

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. • 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) • 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. ⬇️⬇️ 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)
End Sub`````` • 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.

📌  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

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 