Counting consecutive

[Reignman]

In column C2:C5000 I have a list of 20 different names in random orders and different occurances. In column E2:E5000 the same 20 names are randomly listed in different occurances, but not when it occurs in column C.

I need a formula that will return the most consecutive times a particular name occurs in column C before it occurs in column E.

My crude example ...

-C---E
blu grn
red yel
red yel
grn red
red blu
red blu
red grn
yel blu
blu grn
red yel
grn red
red grn

The most consecutive times red occures in column C before it occures in column E is 4 times

Thanks, John

 

[rs2k]

I don't understand your question,
C2,C3 has red occuring, and E4 has red occuring, does that not mean it is twice not 4?
can you post a sample of what you have, and what you want.

Colin.

 

[PA HS Teacher]

Do you want something like the following?

The desired name is in cell I2

In F2: =(C2=$I$2)*(E3<>$I$2)

In F3 and pasted down: =(F2+(G3=G2)*(C3=$I$2))*(E3<>$I$2)

In G2 and pasted down: =COUNTIF(E$2:E2,$I$2)

In I3: = Max(F:F)

Book3
	C	D	E	F	G	H	I
1				Occurances since last E Occurance	E occurances		Desired Name
2	blu		grn	0	0		red
3	red		yel	1	0		4
4	red		yel	2	0
5	grn		red	0	1
6	red		blu	1	1
7	red		blu	2	1
8	red		grn	3	1
9	yel		blu	3	1
10	blu		grn	3	1
11	red		yel	4	1
12	grn		red	0	2
13	red		gr	1	2
Sheet3

 

[chiello]

Below my solution:

Cartel1
	A	B	C	D	E	F	G	H	I	J	K	L
1						red	4	blu	1		ITA	ENG
2					Ocurrences	2		3			CONTA.SE	COUNTIF
3			blu		grn	4	2	5	1		CONFRONTA	MATCH
4			red		yel	11	4	6	0		SCARTO	OFFSET
5			red		yel		1	8	0		RIF.RIGA	ROW
6			grn		red				1		SE	IF
7			red		blu						CONTA.VALORI	COUNTA
8			red		blu						;	,
9			red		grn
10			yel		blu
11			blu		grn
12			red		yel
13			grn		red
14			red		grn
15
Foglio1

Try to explain.

You have data in column C (C3:C14) and E (E3:E14).
Enter in F1 the color to find: "red"
Enter in F2 the formula which counts the occurrences of "red" in E3:E14: 2 for our case
Enter in F3 the formula which provides the row of "red" first occurrence: 4th row for us.
Enter in F4 the formula which gives the row of the second occurrence of "red": row 11
Copy down F4 formula as many times as "Occurrences -2": in our case 2-2=0 -> don't copy
Enter in G3 the formula which gives the number of "red" occurrences till the first occurrence in E
Enter in G4 the formula which count "red" in C between first and second "red" in E
Copy down G4 formula as many times as "Occurrences-1": in this case 2-1=1
Enter G(3+Occurrences), for us G(3+2)=G5 the formula which counts red in C after the last occurrence in E
Enter in G1 the formula which gives the max

 

[Reignman]

I'm looking for exactly what PA HS Teacher has posted, but I'm just wondering if there is a way to consolidate all those multi-cell formulas down into one cell? Thanks, John

 

[PA HS Teacher]

I believe the following array entered formula is equivalent and does not require any additional cells:

=MAX((F2:F12+(COUNTIF(E$3:E$13:E$2:E$12,$I$2)=COUNTIF(E$2:E$2:E$12:E$12,$I$2))*(C3:C13=$I$2:$I$2))*(E3:E13<>$I$2:$I$2))

Confirmed with control + shift + Enter

Adjust the range according, but be careful, notice how one range is shifted down one from the other.

 

[Reignman]

OK ... Looks good 'cept I have one more problem. I don't have the accumulation column represented by F2:F12 at the beginning of your new array. You had added that column for me in your first post which was ...

In F2: =(C2=$I$2)*(E3<>$I$2)
In F3 and pasted down: =(F2+(G3=G2)*(C3=$I$2))*(E3<>$I$2)

Is there a way to add all that into the same array that you've recently posted so I can keep it all in one cell? I'm working with 20+ names on the same sheet that I'm going to use this array with and I don't really have the room to add all those extra columns for each name.

Thanks again, John

 

[fairwinds]

To do it in one cell, try:

=SUM(--(MODE(COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4))=IF(C2:C13=G4,COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4))))

Confirmed with Ctrl + shift + enter.



EDIT: Second thoughts... Formula must be:

=SUM(--(MODE(IF(C2:C13=G4,COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4)))=IF(C2:C13=G4,COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4))))

Confirmed with Ctrl + shift + enter.

However this one will give you an error if there are no consecutives (i.e. if answer should be 1)

Book1
	A	B	C	D	E	F	G	H
1
2			blu		grn
3			red		yel
4			red		yel		red
5			grn		red		4
6			red		blu
7			red		blu
8			red		grn
9			yel		blu
10			blu		grn
11			red		yel
12			grn		red
13			red		grn
14
Sheet4

 

[Reignman]

OK ... Thanks Fairwinds, your 2nd formula works best for what I was looking for ...

=SUM(--(MODE(IF(C2:C13=G4,COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4)))=IF(C2:C13=G4,COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4))))

Now I was wondering what I could add to make it conditional ... Like only include the rows that have the word "color" in column D? I tried adding an IF in there but that didn't seem to work ...

=SUM(IF(D2:D13="color",(--(MODE(IF(C2:C13=G4,COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4)))=IF(C2:C13=G4,COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4))))))

Any suggestions?

 

[fairwinds]

=SUM(--(MODE(IF((C2:C13=G4)*(D2:D13="color"),COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4)))=IF((C2:C13=G4)*(D2:D13="color"),COUNTIF(INDIRECT("E"&ROW(INDIRECT("E2:E13"))&":E13"),G4))))

Confirmed with Ctrl + Shift + Enter