How To Filter Out Duplicates In Google Sheets

See all How-To Articles

How To Filter Duplicate Values in Excel & Google Sheets

This tutorial volition demonstrate how to filter duplicate values in Excel and Google Sheets.

Avant-garde Filter Office

1. Click in the range you wish to apply the filter to (any cell from B3 to B25).
2. In the Ribbon, select Data > Sort & Filter > Advanced.

3. You can either filter the listing in place, or y'all can put the results in a different range on your worksheet.

4. Make sure the check box "Unique Records Only is ticked" and then click OK.

5. The rows with indistinguishable values will be hidden – you will detect that the row numbers are now in blueish and some of the rows do not appear.
6. In the Ribbon, select Data > Sort & Filter > Clear to remove the filter.

Filter Duplicate values to an alternative range

1. Select the range you wish to filter (ex B4:B25).
2. In the Ribbon, select Data > Sort & Filter > Advanced.
3. Select Re-create to another location.

4. Select the Range in the Re-create to: box and brand sure the Unique records only tick box is ticked.

Filter duplicates rows using the Advanced Filter Function in Excel

1. Click in the range you wish to apply the filter to (whatsoever cell from B3 to E11).
2. In the Ribbon, select Information > Sort & Filter > Avant-garde.
3. Filter the list in identify and tick the option to prove unique records just.
4. Click OK.

COUNTIF and the Filter Office in Excel

To filter using COUNTIF and standard Filter function, you need to add an additional helper column to your worksheet.

1. Click in C4 and blazon the following formula.

=IF ( COUNTIF ( $ B $ four : B4 , B4 )=1 , one , 0 )

2. Copy it downward to row 25.
3. If the city appears more than once, a aught will appear next to it when it appears for the second or subsequent time.

4. In the Ribbon, select Habitation > Filter.

5. Dropdown arrows will at present appear in the heading row of your listing.

6. Click the drib-downwardly arrow in the Helper column and select one equally the value to show.

Your listing will now be filtered to only show unique values.

COUNTIF and the Filter Function in Google Sheets

Google sheets does not have an advanced filter.  Yous can filter Duplicate values by using the same formula as you would employ in Excel.

1. Click in C4 and blazon the following formula

=IF ( COUNTIF ( $ B $ four : B4 , B4 )=ane , i , 0 )

2. Copy the formula down to row 25.

3. Select Information>Create Filter from the Menu

4. Remove the tick from the 0 so that you are only going to filter on the 1's.

Click OK to filter the list.

How To Filter Out Duplicates In Google Sheets,

Source: https://www.automateexcel.com/how-to/filter-duplicate-values/

Posted by: walkerwhoduch.blogspot.com

Share this post