filtering with formulas?

[gdmill]

I understand advanced filtering, but I've been trying to figure this
particular filter out for hours and it has me scratching my head (I think
because I'm having trouble using formulas in my filters).

What I'm trying to do is filter one column based on whether data in that
column is equal to data in another column. For example:

A B C D
1 10 12 14 10
2 10 11 15 15
3 12 12 14 12
4 11 15 10 11

Is there an easy way to filter where the data in column A equals the data in
column D? So using the above examples the filter would return rows 1, 3, and
4?

And a more complicated task, where data in column A equals the data in
column D, and is less than data in column C (which should return rows 1 and
4).

Any help is greatly appreciated!

Thanks,
-- gdmill

 

[carlo]

I would put following formula in a different column:
=if(A1=D1)
and then copy down
then you can filter that column with true and you receive row 1,3,4

and for the second task:
=if(and(a1=d1,a1<c1))
same thing as above

hth
Carlo

 

[T. Valko]

Try this:

H = column header

A B C D..............F..........G
1
2 H H H H
3 10 12 14 10
4 10 11 15 15
5 12 12 14 12
6 11 15 10 11

F1 should be left empty. Enter ths formula in F2:

=A3=D3

G1 should be left empty. Enter this formula in G2:

=AND(A3=D3,A3<C3)

Now, for your filter criteria range use either F1:F2 or G1:G2

 

[MrAcquire]

One observation, "where data in column A equals the data in column D, and is
less than data in column C (which should return rows 1 and 4)" is an
incorrect statement. Using your condition, it should return rows 1 & 3, not
rows 1 & 4.

Anyway, on to the desired tasks. First, insert a blank row 1 and create
column headers.

To accomplish your first filter, enter the following in E2 (your first row
of data), copy it down to the rest of the rows, then filter for TRUE in
column E.

=A2=D2

To accomplish your second filter, enter the following in F2 and copy it down
to the rest of the rows, then filter for TRUE in column F.

=AND(A2=D2,A2<C2)