compare values in auto filter

[Guest]

Can anyone tell me if there is a way to compare a value in an autofiltered
list to the value displayed below it? For example:

A B C
6/28/06 4
6/29/06 1
6/29/06 0
7/1/06 5
7/2/06 1
7/7/06 3

If I filter this list for column B >2, it would display rows 1, 4, and 6. I
would like to put a function in column C that would calculate the number of
days between the current row and the filtered row below it. So, that row
would show "3" in row 1 and "6" in column 2. I think something using
SUBTOTAL might work, but I can't figure it out.

The actual list I'll be using is over 5,000 lines, and the distance between
the dates could be very large. I cannot sort the list - it has to stay in
order due to other functions.

Thanks for your help.

 

[Guest]

Perhaps something like this:

With a data list in A1:B5000
Col_A contains dates (A1 is the column heading)
Col_B contains amounts (B1 is the column heading)

C1: Elapsed
D1: Counter

C2: =INDEX($A$2:$A$5000,MATCH(D2+1,$D$2:$D$5000,0))-A2
D2: =SUBTOTAL(3,$A$1:A2)
Copy those formula down as far as needed

(Adjust range references in those formulas to suit your situation)

Now filter the list. The Elapsed column (formatted as numbers, not dates)
should display the elapsed days.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Another play to tinker with ..
this one lays the desired results direct into col C (wo autofilter)

Assuming data in cols A and B, from row1 down

Let's assume the criteria's numeric value
for col B will be input into say, E1, viz. in E1: 2

Put in C1:
=IF(D1="","",IF(ISERROR(LARGE(D,COUNT($D$11)+1)),"----",INDEX(A:A,MATCH(LARGE(D,COUNT($D$11)+1),D,0))-A1))

Put in D1:
=IF(OR(A1="",B1="",$E$1=""),"",IF(B1>$E$1,10^5-ROW(),""))

Select C11, fill down to the last row that source data is expected

Col C will return the required results, viz: 3 in C1, 6 in C4, etc, with
"blanks" in between. As a flag/indicator, "----" will be returned in the
last row in col C where col B contains a value satisfying the criteria. So
for the sample data posted, we'd get a "----" returned in C6.

(Hide away the criteria col D if desired)

 

[Guest]

Thanks for the suggestions - I'll try these out and see how they work. I
appreciate the help.

 

[Guest]

Thanks for the feedback
Do drop us a line here later how it went for you ..