CODEWITHSUNDEEP
excelexcel-2013
davimusprime
davimusprime

Reputation: 119

Advanced filter that compares multiple columns

I have a table.

      A    |   B         |   C         |   D         |   E   
 1    ID   |   Value 1   |   Value 2   |   Value 3   |   Value 4
 2    1    |   15        |   7         |   18        |   12
 3    2    |   6         |   19        |   11        |   20
 4    3    |   24        |   10        |   12        |   9
~plus about 10,000 more

I trying to create an advanced filter that shows results where Value 1 > Value 2 > Value 3 > Value 4. Can't wrap my head around the criteria table.

Upvotes: 1

Views: 505

Answers (2)

PA.
PA.

Reputation: 29369

Add a new column F, then type this in F2

=AND((B2>C2),(C2>D2),(D2>E2))

copy and paste in the rest of the column F3..F10000

now, create a filter for column F

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149325

shows results where Value 1 > Value 2 > Value 3 > Value 4

Here is a simple way to do it. In cell F2, insert this formula

=IF(AND(D2>E2,C2>D2,B2>C2),"True","")

and copy it down.

enter image description here

Now simply filter it on True and copy the filtered range to a new worksheet.

Upvotes: 2

Related Questions