Multi-line sequence counting...

[wabbit_47]

Howdy guru's!

Bit of a tricky one, but really hoping someone can help me crunch some traffic signal data.

In an exported .csv spreadsheet, I get a separate row for each time a phase is activated, with either phase A, B or C in the Phase column.

What I need to do is establish how often each sequence occurs within the column before returning to <A>. For example...

<A>
B
<A>
B
<A>
B
C
<A>
B
C
<A>
B
<A>
C

How often did ABC occur; how often did AB occur; how often did AC occur?

Doth any formula exist that could count the sequence that occurs before the sequence returns to an <A> value??

Thanks!
Wabbit_47

 

[jimrward]

ok assuming your data starts in A1, create a helper column in B1 and try the following which will produce 123 for a full house, where A=1, B=2, C=3

=IF(A1="<A>",IF(AND(A2="B",A3="C"),"123",IF(A2="B","12",IF(A2="C","13","1"))),"")

drag the formula down for all of you data

You can then use COUNTIF on your helper column to look for 123, 12, 13, 1

sequence.xlsx
	A	B
1	<A>	12
2	B
3	<A>	12
4	B
5	<A>	123
6	B
7	C
8	<A>	123
9	B
10	C
11	<A>	12
12	B
13	<A>	13
14	C
15	<A>	1
16	<A>	123
17	B
18	C
Sheet1

Cell Formulas
Range	Formula
B1:B16	B1	=IF(A1="<A>",IF(AND(A2="B",A3="C"),"123",IF(A2="B","12",IF(A2="C","13","1"))),"")

 

[wabbit_47]

ok assuming your data starts in A1, create a helper column in B1 and try the following which will produce 123 for a full house, where A=1, B=2, C=3

=IF(A1="<A>",IF(AND(A2="B",A3="C"),"123",IF(A2="B","12",IF(A2="C","13","1"))),"")

drag the formula down for all of you data

You can then use COUNTIF on your helper column to look for 123, 12, 13, 1




Thank you sooooo much for this! Absolutely perfect! Great solution

 

[jimrward]

Thanks for the feedback
Are you happy with COUNTIF is it giving correct results

 

[wabbit_47]

Thanks for the feedback
Are you happy with COUNTIF is it giving correct results

It sure is...the COUNTIF function then means I can crunch it further to calculate percentages of how often each possible cycle sequence happens and apply the percentage factor to extrapolate the sequence trends and adjust timings

Very cool in a non-cool kinda way

 

[Peter_SSs]

Could you just use the letters directly instead of going via a numerical code?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

20 07 08.xlsm
	A	B
1	<A>	AB
2	B
3	<A>	AB
4	B
5	<A>	ABC
6	B
7	C
8	<A>	ABC
9	B
10	C
11	<A>	AB
12	B
13	<A>	AC
14	C
15	<A>	A
16	<A>	ABC
17	B
18	C
Sequence

Cell Formulas
Range	Formula
B1:B18	B1	=IF(A1="<A>","A"&IF(A2="<A>","",SUBSTITUTE(A2&A3,"<A>","")),"")

 

[wabbit_47]

A

Could you just use the letters directly instead of going via a numerical code?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

20 07 08.xlsm
	A	B
1	<A>	AB
2	B
3	<A>	AB
4	B
5	<A>	ABC
6	B
7	C
8	<A>	ABC
9	B
10	C
11	<A>	AB
12	B
13	<A>	AC
14	C
15	<A>	A
16	<A>	ABC
17	B
18	C
Sequence

Cell Formulas
Range	Formula
B1:B18	B1	=IF(A1="<A>","A"&IF(A2="<A>","",SUBSTITUTE(A2&A3,"<A>","")),"")

I shall do that, thanks for the tip

 

[Peter_SSs]

You're welcome.