Filter excel data excluding the duplicates

Question

I have two columns of data, I want to exclude the duplicates and keep only one registry of each sort, how can I do that?

For example having this set of data:

filtering it and get:

what is the filter I should use?

Answer 1

The quickest way to filter out these duplicates might be to use two helper columns.

In the first helper column use a formula to concatenate the two cell values. In another helper column count how often the concatenated value has appeared above the current row and flag the cell. Consider the screenshot:

The formula in C2 and copied down is

=A2&B2

The formula in D2 and copied down is

=IF(COUNTIF($C$1:C2,C2)>1,"","keep")

Now you can use a filter on column D and show only the rows with the value "keep".

Note: if you want to remove the duplicates altogether, you can use the Remove Duplicates command on the Data ribbon. You don't need to concatenate anything. Just select a cell in the two columns, click Data > Remove Duplicates and make sure both columns are ticked in the list. Hit OK and the duplicates are gone.

Answer 2

It sounds to me like you want to remove the duplicates. In that case, you would not use a filter, rather it is as simple as using the Remove Duplicate feature found on the Data tab.

Highlight the data, select the Data tab, click Remove Duplicates, click OK.

Will leave you with the results you are looking for in your example.