This blog post describes how to filter unique distinct rows using an excel table. This post shows you how to do it using array formulas: Filter unique distinct records with a condition in excel 2007

The difference with today´s blog post is that you also can add more filters to your excel table, making the excel table a lot more useful.

What are unique distinct rows/records?

Unique distinct rows are all rows but duplicate rows are removed. See picture below.

Excel table example

The excel table, shown to the left, has some duplicate rows. I have highlighted these rows.

We are now going to add a new column to the table. Select cell D2 and type this formula:

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)

Press Enter.

 

 

 

 

 

Filter unique distinct rows

  1. Click black arrow near header "Column1"
  2. Select 1
  3. Click ok!

Add another filter to the excel table

  1. Click black arrow near header "Products"
  2. Select "AA"
  3. Click OK!

Only unique distinct rows containing "AA" in "Products" column are shown. See picture below.

Download excel 2007 *.xlsx file

Excel-table-Filter-unique-distinct-records-with criteria.xlsx