Annyca Tabassum
During data analysis, data repetition is a significant problem. To get rid of this problem, you must create a unique dataset free from any type of redundant data. So, in this article, we will learn about Google Sheets find unique values between 2 columns.
A Sample of Practice Spreadsheet
You can download spreadsheets from here and practice.
3 Ways to Find Unique Values Between 2 Columns in Google Sheets
In Google Sheets, there are some easy ways to find unique values quickly and get rid of data redundancy. The most common ways are using the ‘Remove Duplicates’ tools and applying the UNIQUE function. To explain the techniques, we develop a dataset containing the Product Name and Sales columns.
1. Using ‘Remove Duplicates’ Tool
It is the easiest way to find and remove duplicates and get unique values from a range of data. To apply the method,
Steps:
- First, select the entire dataset columns. Here, we select the range B4:C12. In the dataset, we highlight the duplicate value for better understanding.
- Now, go to the Data feature and select Data cleanup > Remove duplicates.
- In the Remove duplicates dialogue box if you want to select the heading then check on the ‘Data has header row’ option. In the Column to analyze option you can select any columns from where you want to remove duplicate values. Here, we select all columns.
- After that, click on the Remove duplicates button.
- Now, you will find the number of duplicate rows and unique rows contained in the dataset. Press OK to run the method.
- Finally, you will get the unique values only in the dataset.
As you can see, only the first rows of the duplicate entries remain, leaving only the unique values between the two columns.
Read More: Use REGEXEXTRACT Function Between Two Characters in Google Sheets
Similar Readings
- How to Remove Spaces Between Words in Google Sheets
- Calculate Number of Years Between Two Dates in Google Sheets
- How to Filter Between Two Dates in Google Sheets
- Difference Between COUNT and COUNTA in Google Sheets
- How to Use IF Condition Between Two Numbers in Google Sheets
2. Applying UNIQUE Function
Though the remove duplicate tools are the simplest way to get unique values, it will change your original dataset. So, if you don’t want to change your dataset at the same time, you want to sort your data by unique values then the UNIQUE function will be the best option.
Steps:
- First, we create a new table to show unique values.
- Then, select cell E5 and insert the UNIQUE function.
- Now, apply the range B5:C12 from where duplicates will be found.
- Finally, press ENTER, and you will find the unique values in the new table.
=UNIQUE(B5:C12)- Here, the function looks for the unique value from the range B5:C12.
- You can also apply the same function to get the unique values with the column header as well.
Read More: Generate Random Numbers or Text Between Limits in Google Sheets
3. Inserting FLATTEN Function
The FLATTEN function is handy when you want to accumulate data from different columns. By combining FLATTEN function with the UNIQUE function you can achieve unique values.
Steps:
- In the beginning, develop a dataset that contains the Team A and Team B columns. We also create a result column to show the outcome of the function.
- Then select cell E5 and insert the FLATTEN function.
- Now add the entire dataset as the range.
- Press ENTER and you will find all values of the two columns in a list. The values contain the duplicates also.
=FLATTEN(B5:C9)
- So, to get only unique values insert the UNIQUE function before the FLATTEN function. And finally, press ENTER and get only unique values.
=UNIQUE(FLATTEN(B5:C9))
Read More: How to Find Missing Values Between Two Columns in Google Sheets
Things to Remember
- All cells must have the same number of rows and columns.
- Carefully insert the range in the function.
- Merge cells are not applicable to finding unique values.
Conclusion
We believe that this article will help you to find unique values between two columns and minimize your effort of data analysis in Google Sheets. For further knowledge about Google Sheets and other functions, you can visit the OfficeWheel website and make yourself more proficient.
Related Articles
- How to Calculate Hours Between Two Times in Google Sheets
- Conditional Formatting Between Two Values in Google Sheets
- How to Insert Lines Between Cells in Google Sheets
- Find Number of Months Between Two Dates in Google Sheets
- How to SUMIF Between Two Dates in Google Sheets (3 Ways)
- Calculate Percentage Difference Between Two Numbers in Google Sheets
- How to Calculate Time Between Dates in Google Sheets (6 Ways)
