How to filter certain data?

Posted by Russell on September 18, 2001 10:34 PM

Hi,

I would like to do the following 2 things.

1: Filter data which in one column is greater than another column.

2: Select only the row which has a particular name in one column and the maximum date for all rows with that name in another column

Thanks

Posted by Juan Pablo on September 19, 2001 7:03 AM

Hello, ok, onto your questions.

1. I would create another column that is the difference between them. For example if yor data is in Columns A and B, put in C2 =A2-B2 and copy down as needed. That way in Column C every value that is greater than 0 tells you that Column A is greater than Column B.

2. For this you can use an Array Formula like this. Say names are in A2:A50, dates in E2:E50

Put in A52 the Column heading of the names column ("Names" for example), and in B52 the heading of the dates column ("Dates"). Now in A53 put the name you want to filter, and in B53 put this Array formula (Remember, Control Shift Enter to enter an array formula)

{=MAX(IF(A2:A50=A53,E2:E50))}

That way you can use Advanced filter to get only the row with that Name and the maximum date for that name.

Juan Pablo