Function Help?

[dkenebre]

I need a function that will count back all previous rows for th
farthest digit back in the R, S and T cells and place it in the
column. The furthest digit back: each of the 3 cells being evaluated (
S and T) have single digit values which have occurred in previous R,
T cells, but the value which last appeared in the earliest row is th
value which the furthest back. I want the function to count how man
rows since the last time that value last occurred.

See sample below the N column has the correct answers:

Example:
.................R..S...T........N
row 11:....9...0...6
row 12:....7...5...7
row 13:....7...3...4
row 14:....4...0...9........3
row 15:....0...5...6........4

on row 14 the 0 and 9 are equally the farthest digits back,
at 3 rows.
on row 15 the 6 is the farthest digit back at 4 rows.

I would appreciate your hel

 

[Otto Moehrbach]

Wouldn't the 7 in row 13 fit the criteria at 1?
I don't think you're going to find a function that will do this. I think it
will take a macro. If you're interested in using a macro for this, post
back.

 

[Alan Beban]

With all the :'s and .'s and Columns R, S & T to the left of Column N,
it's hard to see how your data is laid out; can you provide an
illustration that shows where different values are in cells of a worksheet?

Alan Beban

 

[dkenebre]

Okay if a macro is what I need that is fine. I have enclosed a sample o
my chart.
In my sample the evaluation cells for each row is R, S and T.
I would like for the macro to ignore the black rows in it's count
which occurs every 13 rows. I need a macro that will count back al
previous rows for the farthest digit back in the R, S and T cells an
place it in the N column.
(The furthest digit back: each of the 3 cells being evaluated (R S an
T) have single digit values which have occurred in previous R, S
cells, but the value which last appeared in the earliest row is th
value which the furthest back. I want the macro to count how many row
since the last time that value last occurred. The N column in my sampl
has the correct answers.)
2nd:
I would like the macro to perform the same farthest digit evaluation o
every orange R,S and T cells, ignoring the black row and the blue row
and place the result in column O. I have the correct answers in th
sample sheet's column O.
3rd
I would like the macro to perform the same farthest digit evaluation o
every blue R,S and T cells, ignoring the black row and the orange row
and place the result in column O. I have the correct answers in th
sample sheet's column O.

Thank

+----------------------------------------------------------------
| Attachment filename: book1c.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=361000
+----------------------------------------------------------------

 

[Alan Beban]

I don't open attachments.

Because the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to my workbook, I put your
array of numbers in R11:T15; in U1 I entered

=ROW()-MIN(ColumnVector(ArrayMatch(R11,$R$11:$T$15),1)) and filled
across to W1 and down to U5:W5

Then in N11 I enterd =MAX(U1:W1) and filled down to N15; the return
values were 0,0,1,3,4

Alan Beban

 

[dkenebre]

Can anyone figure out this macro