Skip to main content

How to Use UNIQUE and SORT for Clean Data in Google Sheets

How to Use UNIQUE and SORT for Clean Data in Google Sheets

How to Use UNIQUE and SORT for Clean Data in Google Sheets

Google Sheets is an excellent tool for data management, and two functions that help you clean and organize your data effortlessly are UNIQUE and SORT. These functions can help you remove duplicates and arrange your data in a structured manner, making it much easier to analyze and visualize.

🔄 1. The UNIQUE Function: Removing Duplicates

The UNIQUE function is designed to filter out duplicate values from a given range, leaving you with only distinct values. This can be very useful for cleaning up lists of names, emails, or any other repetitive data.

=UNIQUE(range)

For example, if you have a list of salespersons' names and want to list only the unique names, you would use the following formula:

=UNIQUE(A2:A20)

This formula will return all the unique names from the range A2:A20 and remove any duplicates.

🔀 2. The SORT Function: Organizing Your Data

The SORT function allows you to arrange your data in ascending or descending order based on one or more columns. This is especially helpful for sorting numerical values, dates, or even alphabetically ordered lists.

=SORT(range, sort_column, is_ascending)

In this formula:

  • range: The range of cells you want to sort.
  • sort_column: The column number on which you want to base the sorting.
  • is_ascending: TRUE for ascending order and FALSE for descending order.

For example, if you want to sort the list of sales amounts from highest to lowest, you would use:

=SORT(A2:B20, 2, FALSE)

This formula will sort the data in columns A and B based on column B in descending order.

⚡ 3. Combining UNIQUE and SORT for Efficient Data Cleaning

One of the most powerful ways to clean your data in Google Sheets is by combining the UNIQUE and SORT functions. This approach allows you to both remove duplicates and sort the results for easy analysis.

=SORT(UNIQUE(A2:A20), 1, TRUE)

This formula will first remove any duplicates from the range A2:A20 and then sort the remaining unique values in ascending order.

📅 4. Real-Life Example: Sorting and Removing Duplicates from a List of Event Attendees

Let’s say you have a list of event attendees, but some people registered multiple times. You can use the following formula to get a sorted, unique list of attendees:

=SORT(UNIQUE(B2:B100), 1, TRUE)

This formula will remove duplicate names from the range B2:B100 and sort them alphabetically.

🔧 5. Advanced Usage: Sorting by Multiple Columns

You can also sort your data by multiple columns. For example, to sort sales data first by salesperson name and then by sales amount, you can use:

=SORT(A2:C20, 1, TRUE, 3, FALSE)

This will first sort by the salesperson's name (column 1) in ascending order, and then by sales amount (column 3) in descending order.

📥 Downloadable PDF

Tags: UNIQUE function, SORT function, clean data, remove duplicates, sort data, google sheets formulas, google sheets tutorial

Comments

Post a Comment

Popular posts from this blog

How to activate office 2016 using script ??

How to activate office 2016 using script ?? 1. Copy the following script in notepad and save as .bat . 2. Run the bat file as administrator. @echo off title Permanently Activate Office 365 ProPlus for FREE - Somethingos.blogspot.incom&cls&echo ============================================================================&echo #Project: Activating Microsoft software products without software&echo ============================================================================&echo.&echo #Supported products: Office 365 ProPlus (x86-x64)&echo.&echo.&(if exist "%ProgramFiles%\Microsoft Office\Office16\ospp.vbs" cd /d "%ProgramFiles%\Microsoft Office\Office16")&(if exist "%ProgramFiles(x86)%\Microsoft Office\Office16\ospp.vbs" cd /d "%ProgramFiles(x86)%\Microsoft Office\Office16")&(for /f %%x in ('dir /b ..\root\Licenses16\proplusvl_kms*.xrm-ms') do cscript ospp.vbs /inslic:"..\root\Licenses16...
2 comments
Read more

How to add or delete user in CMD??

How to add or delete user in CMD?? 1. Run CMD as Administrator . 2. Type following commands to add a use :           >net user test /add                >net user test /enable:yes         >net user test 123@test here test is a user and 123@test is password. 3. To delete :          >net user test /delete
Post a Comment
Read more

Activate windows 10 without software, Using .bat script

Here is a way to activate Windows 10 using Script. # Make sure you're connected with Internet. #  Open notepad,  paste the script save as file abc.bat and run as administrator. @echo off title Windows 10 &cls&echo ************************************ &echo Copyright: Sudhir: 2018  &echo.&echo Supported products:&echo - Windows 10 Home&echo - Windows 10 Professional&echo - Windows 10 Enterprise,&echo - Windows 10 Education&echo.&echo.&echo ************************************ &echo Windows 10 activation... cscript //nologo c:\windows\system32\slmgr.vbs /ipk TX9XD-98N7V-6WMQ6-BX7FG-H8Q99 >nul cscript //nologo c:\windows\system32\slmgr.vbs /ipk 3KHY7-WNT83-DGQKR-F7HPR-844BM >nul cscript //nologo c:\windows\system32\slmgr.vbs /ipk 7HNRX-D7KGG-3K4RQ-4WPJ4-YTDFH >nul cscript //nologo c:\windows\system32\slmgr.vbs /ipk PVMJN-6DFY6-9CCP6-7BKTT-D3WVR >nul cscript //nologo c:\windows\system32\slmgr.vbs /i...
Post a Comment
Read more