Cell value reverts to 'Q1' if value in another column changes

[fingermouse]

As per the question, i have two columns, one entitled 'ref no' and another entitled 'qtr'.

There are about 400 rows in the sheet, with multiple ref numbers.

What I want to do is revert to a value of 'Q1' in the 'qtr' column every time the 'ref no' column value changes. (text in red)

How can this be done?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref no[/TD]
[TD]Qtr[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Q2[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Looks longer than necessary but works ok. Put this in B2 and copy down:

=IF(OR(NOT(ISNUMBER(0+SUBSTITUTE(B1,"Q",""))),B1="Q4",A2<>A1),"Q1","Q"&SUBSTITUTE(B1,"Q","")+1)

 

[FormR]

And here is another option that you can try:

Excel 2013/2016
	A	B
1	Ref no	Qtr
2	001	Q1
3	001	Q2
4	001	Q3
5	001	Q4
6	001	Q1
7	001	Q2
8	001	Q3
9	002	Q1
10	002	Q2
Sheet1

Cell Formulas
Range	Formula
B2	=IF(OR(A2<>A1,B1="Q4"),"Q1","Q" & RIGHT(B1)+1)

 

[fingermouse]

Thanks Gents!!! Perfect and very much appreciated!!