List and filter distinct, unique, and duplicate rows in Excel

What is it?

With the Filter Duplicates utility you can identify, highlight, label, and filter rows within a list of data for distinct, unique, and duplicate rows in Excel. You can select which columns to utilize for the analysis and then when viewing the results can choose whether to view the distinct, unique, or duplicate rows found. You can also choose to cycle through 1 by 1 for each set of distinct, unique, or duplicate rows found.

Utility Definitions:

Data Tab:
List to Filter:
The List to Filter is the list of data that will be analyzed for distinct, unique, and duplicate rows.

?
Select the ? button to view this documentation page

Criteria Columns Tab:
Columns For Analysis:
The columns selected within the Columns For Analysis list are combined when analyzing the rows within the list of data.

Dynamic:
Toggle on the Dynamic setting to have each row define it's own column matching criteria based on the columns that contain values within its row. This allows you to identify which rows are distinct, unique, and duplicate for data that is defined with several columns of each row that may represent a common grid of values that are either defined or not.

Applied Manipulation
The Applied Manipulation setting is a way to pre-process the rows cell's value without affecting the original content. This setting is defined within the Content Manipulator utility. For example, let's assume you want to analyze a list of data based on two of it's columns but you want the results to be based on case insensative values so you've defined a Content Manipulator setting that converts text to upper case. When the Filter Duplicates is run, a cell's value within the defined columns is considered the same if it contains TEST, test, or any variation of upper and lower case - TeSt thus basing the results on the manipulated data without actually affecting the original data.

Result Tab:

Information
Distinct Rows:
Distinct is the number of rows found that are either unique or first row of its duplicate row set.

Unique Rows:
Unique is the number of non duplicate rows found

Exact Unique Rows:
Exact Unique is enabled when the Dynamic setting is toggled on. This represents the number of rows found that contained the exact same row values within the dynamic column criteria but are unique relative to the entire data.

Sets of Duplicates:
Sets of Duplicates is the number of different sets of duplicate rows found

Sets of Exact Duplicates:
Sets of Exact Duplicates is enabled when the Dynamic setting is toggled on. This represents the number of different sets of duplicate rows found that contained duplicates with the exact same row values within the dynamic column criteria.

Total Duplicate Rows:
Total Duplicate Rows is the complete number of duplicate rows found

Show
Distinct:
Select Distinct to view or show all of the distinct rows found

Unique:
Select Unique to view or show all of the unique rows found

Exact Unique:
Select Exact Unique to view or show all of the exact unique rows found

Duplicates:
Select Duplicates to view or show all duplicate rows found

Exact Duplicates:
Select Exact Duplicates to view or show all exact duplicate rows found

List Box:
Select the List Box to select a number to view each set of unique or duplicate rows found

All:
Select All to view or show all of the data list

Actions Tab:

Label
Column:
Enter the column that you want the text from the Text field to be entered in.

Text:
Enter the text that you want to be placed in each visible result row.

Join Row Values
Join Column:
Enter the column that you want to join the row values from.

Unique:
Toggle on Unique when you want the joined or concatenated row values to only join unique values.

Result Column:
Enter the column that you want the results to be placed in.

Delimiter:
Enter the Delimiter to divide the joined results between.

Applied Manipulation
The Applied Manipulation setting is a way to apply text manipulations for the rows cell's value within the defined Join Column. This setting is defined within the Content Manipulator utility - change all text to upper case prior to joining for example. This does not affect the original cell values only the values placed in the Result Column.

Highlight Row
Color:
Select the "Select here to choose a color" button to select a color to hightlight the visible result rows.

Label:
Toggle on the Label checkbox to enable Labeling during Apply

Highlight Row:
Toggle on the Highlight Row to enable row highlighting during Apply.

Join Row Values:
Toggle on Join Row Values to enable the joining or concatenating of the defined row values settings during Apply.

Apply:
Select Apply to perform the enabled Actions.

Documentation

What is it?

Software

Compare

Text Editing

Content Analysis

Files

Hierarchy

Filter

Utilities

Videos - YouTube

Contact Us