Filter out rows where multiple values are the same.

[JohnnyDavidson]

Say there was a table like this:


. 1 2 3
A x 2 a
B y 7 b
C x 2 c
D x 3 d
E z 6 e


I want to filter out all rows for example where columns 1 & 2 in a row are the same as another row. So it would only show that row once. The end result would be like this:


. 1 2 3
A x 2 a
B y 7 b
C x 3 d
D z 6 e


How would I do that?

[JohnnyDavidson]

I attached a table with some misc data. The last row of the table should be hidden (not deleted) because columns 1 & 2 are the same as the first row.

[JapanDave]

Hey Johnny,

I can't see the pattern. The row where C is is seems to have its data changed? It went from
C x 2 c
To
C x 3 d
Idon' understand how it can do that?

[JohnnyDavidson]

The pattern is that the row where C was got deleted, the reason being because columns 1 & 2 on C were a duplicate with A.

To give you a better idea of what I mean, excel actually has a built in function that does this, except it only works to remove rows, and not filter. Here is a screenshot of the built in function that they have:

http://img215.imageshack.us/img215/9957/20911937.png

But again, only works for removing, not filtering which is what I want.

[Haseeb Avarakkan]

How about Advanced Filter,

Insert 3 rows above the heading & use COUNTIFS in A2 like,

=COUNTIFS(Table1[Column1],A5,Table1[Column2],B5)>1

Then advanced filter, select the range, select criteria range as A1:A2

See the attached.