Counting consecutive

Reignman

New Member
Joined
Feb 13, 2005
Messages
7
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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
CDEFGHI
1Occurances since last E OccuranceE occurancesDesired Name
2blugrn00red
3redyel104
4redyel20
5grnred01
6redblu11
7redblu21
8redgrn31
9yelblu31
10blugrn31
11redyel41
12grnred02
13redgr12
Sheet3
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Below my solution:
Cartel1
ABCDEFGHIJKL
1red4blu1ITAENG
2Ocurrences23CONTA.SECOUNTIF
3blugrn4251CONFRONTAMATCH
4redyel11460SCARTOOFFSET
5redyel180RIF.RIGAROW
6grnred1SEIF
7redbluCONTA.VALORICOUNTA
8redblu;,
9redgrn
10yelblu
11blugrn
12redyel
13grnred
14redgrn
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

New Member
Joined
Feb 13, 2005
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

New Member
Joined
Feb 13, 2005
Messages
7

ADVERTISEMENT

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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDEFGH
1
2blugrn
3redyel
4redyelred
5grnred4
6redblu
7redblu
8redgrn
9yelblu
10blugrn
11redyel
12grnred
13redgrn
14
Sheet4
 

Reignman

New Member
Joined
Feb 13, 2005
Messages
7
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,970
Messages
5,599,108
Members
414,288
Latest member
horizon2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top