Count "last value" occurances

[DutchKevin]

Hello friends of excel

For planning reasons we maintain an ever changing matrix of values.
I would like to use a formula to count how many times the last value in a row has appeared at the end of that row.

For example.
My data-row looks something like this:
A,A,B,B,B,C,D,D,B,B,B,B,A,A,C,B,B
I currently count the non blank cells in the row and then use Index to pick then the last value (=B) into a helper column.
How would you get a formula (?) to know that last value (=B) happened two times before the end of the data in the row?
The nr of values increase every week by one.

Thanks for helping!
Kevin
​

 

[Haldoug]

=RIGHT(A12)=MID((A12),(LEN(A12)-2),1), if the data is in cell a12.
This will tell you if the last two letters are the same in a12 no matter how long the data is, if I understand what you are looking for.

 

[DutchKevin]

Hi Haldoug,
Thanks for replying.
I tried your welcome suggestion but, unforfunately, my data is in seperate cells, so i think that your suggestion does not apply then?

Each next week, in this example, the row expands with another cell, with another letter.

I'm looking in the direction of;
- in a row, find last cells position [solved]
- then (optional) link that last cell's value into a helper column [also solved]
- count cells backwards from last cells position until the cell-value is different. [problem]

Do you have any other ideas?

Thanks, Kevin

 

[Aladin Akyurek]

Hello friends of excel

For planning reasons we maintain an ever changing matrix of values.
I would like to use a formula to count how many times the last value in a row has appeared at the end of that row.

For example.
My data-row looks something like this:
A,A,B,B,B,C,D,D,B,B,B,B,A,A,C,B,B
I currently count the non blank cells in the row and then use Index to pick then the last value (=B) into a helper column.
How would you get a formula (?) to know that last value (=B) happened two times before the end of the data in the row?
The nr of values increase every week by one.

Thanks for helping!
Kevin
​

Given the following 3 rows...

A,A,B,B,B,C,D,D,B,B,B,B,A,A,C,B,B
A,D,B,@,B
B,B,B,V

where @ stands for an empty/blank cell, what is the desired outcome?

 

[T. Valko]

Hello friends of excel​

For planning reasons we maintain an ever changing matrix of values.
I would like to use a formula to count how many times the last value in a row has appeared at the end of that row.​

For example.
My data-row looks something like this:
A,A,B,B,B,C,D,D,B,B,B,B,A,A,C,B,B
I currently count the non blank cells in the row and then use Index to pick then the last value (=B) into a helper column.
How would you get a formula (?) to know that last value (=B) happened two times before the end of the data in the row?
The nr of values increase every week by one.​

Thanks for helping!
Kevin​

Try this...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	A	A	B	B	B	C	D	D	B	B	B	B	A	A	C	B	B
2	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_
3	B	2	_	_	_	_	_	_	_	_	_	_	_	_	_	_	_
Sheet1

Enter this formula in A3. This will return the right-most TEXT entry from the range A1:Q1.

=LOOKUP("zzzzz",A1:Q1)

Enter this array formula** in B3. This will return the current "streak".

=LOOKUP(1E100,FREQUENCY(IF(A1:Q1=A3,COLUMN(A1:Q1)),IF(A1:Q1<>A3,COLUMN(A1:Q1))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[DutchKevin]

Re: Count "last value" occurances [solved]

Hello T.Valko

This is exactly what I needed.
I'm a bit puzzled how it actually works, especially since its in an array formula, but for sure this solves the question.

Thanks very much.

Kevin

 

[DutchKevin]

Re: Count "last value" occurances [solved]

For everybody else looking for something like this i paste my final formula below.
It works from one cell (A1 for instance) and evaluates the range (C1:XFD1)

- data starts in Cell C1 and then fills the row as long as you want.

=LOOKUP(1E+100;FREQUENCY(IF(C1:(OFFSET(B1;0;COUNTA(C1:XFD1)))=OFFSET(B1;0;COUNTA(C1:XFD1));COLUMN(C1:(OFFSET(B1;0;COUNTA(C1:XFD1)))));IF(C1:(OFFSET(B1;0;COUNTA(C1:XFD1)))<>OFFSET(B1;0;COUNTA(C1:XFD1));COLUMN(C1:(OFFSET(B1;0;COUNTA(C1:XFD1)))))))

This works for text-values as well as number-values in the cells

Note that the used offset function uses B1 as reference cell
Note also that you need to enter this as an array formula:
Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[T. Valko]

Re: Count "last value" occurances [solved]

Hello T.Valko

This is exactly what I needed.
I'm a bit puzzled how it actually works, especially since its in an array formula, but for sure this solves the question.

Thanks very much.

Kevin

You're welcome. Thanks for the feedback!