Indexing, Counting, Lookup

[jimblimm]

my setup:
a(day) b(value)

[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]t[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]t[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]r[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]u[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]v[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]w[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]r[/TD]
[/TR]
</tbody>[/TABLE]

c1(repeated value)
e

d1(times repeated)
2

e1(value that appears after current streak of (d1) which is 2)
t

i am looking for a formula that will give me the last (day) that this in particular event occurred.

my answer would be 3 (in reference to the numerical column a(days).

Note: the values in d1 and can range anywhere between 1 and 50
the value in e1 could vary also.

 

[Marcelo Branco]

Maybe this array formula (Excel 2007 or higher)

=IFERROR(INDEX(A:A,LARGE(IF(COUNTIF(OFFSET($B$1,ROW($B$1:$B$100)-ROW($B$1),0,$D$1),$C$1)=$D$1,IF(T(OFFSET($B$1,ROW($B$1:$B$100)+$D$1-1,0))=$E$1,ROW($A$1:$A$100)+$D$1)),1)),"Not Found")

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

M.

 

[jimblimm]

The formula kinda works but its just one problem.
Say for instance i have the string

b1:b7

1
2
3
4
5
6
7

a1: a6
e
e
t
e
e
e
t

c1(repeated value)
e

d1(times repeated)
2

e1(value that appears after current streak of (d1) which is 2)
t

the formula would give me 7, but it should give me 3 because the string that i want is
e
e
t

not
e
e
e
t

 

[Marcelo Branco]

I was taking this
e
e
e
t
as a valid sequence for c1= e; d1=2; e1=t

hmm...hard one...

I'll try, but i'm not visualizing an easy solution using formulas.

M.

 

[Marcelo Branco]

Including a row with headers like below, maybe...

A B C D E F (headers in row 1)[TABLE="width: 296"]
<colgroup><col style="width: 48pt;" span="5" width="64"> <col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" width="74"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Day[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Value[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Search[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Repeat[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Follow[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]Result[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent"]e[/TD]
[TD="class: xl63, bgcolor: transparent"]e[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]t[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]e[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]t[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]e[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl63, bgcolor: transparent"]e[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl63, bgcolor: transparent"]e[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl63, bgcolor: transparent"]t[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]v[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl63, bgcolor: transparent"]w[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent"]r[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


Array formula in F2
=IFERROR(INDEX(A:A,LARGE(IF(T(OFFSET($B$1,ROW($B$1:$B$99)-ROW($B$1),0))<>$C$2,IF(COUNTIF(OFFSET($B$2,ROW($B$2:$B$100)-ROW($B$2),0,$D$2),$C$2)=$D$2,IF(T(OFFSET($B$2,ROW($B$2:$B$100)-ROW($B$2)+1+$D$2-1,0))=$E$2,ROW($A$2:$A$100)+$D$2))),1)),"Not Found")

Ctrl+Shift+Enter

M.

 

[jimblimm]

Well what do you know....That Did it!! Thanx You are a Life saver

 

[Marcelo Branco]

Well what do you know....That Did it!! Thanx You are a Life saver

Great!!!

You are welcome and thanks for the feedback!

M.

 

[jimblimm]

One thing. . What if the values I wanted was numbers instead of text what do I need to change in this formula to make it work

 

[Marcelo Branco]

Try

=IFERROR(INDEX(A:A,LARGE(IF(N(OFFSET($B$1,ROW($B$1:$B$99)-ROW($B$1),0))<>$C$2,IF(COUNTIF(OFFSET($B$2,ROW($B$2:$B$100)-ROW($B$2),0,$D$2),$C$2)=$D$2,IF(N(OFFSET($B$2,ROW($B$2:$B$100)-ROW($B$2)+1+$D$2-1,0))=$E$2,ROW($A$2:$A$100)+$D$2))),1)),"Not Found")

Ctrl+Shift+Enter

M.