looking for help with a formula...

[fingermouse]

Hi,

I'm sure excel can do this but not sure where to start.

Please refer to the table below. Basically, I need a formula in the 'qtr' column which will reset to 'Q1' any time the value of the reference number in the 'ref' column changes.

Any help much appreciated! Thanks, Cal.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[TD]Qtr[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]26/10/2015[/TD]
[TD]25/01/2017[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/01/2016[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]25/04/2017[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/04/2016[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]25/07/2017[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/07/2016[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]25/10/2017[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]26/10/2015[/TD]
[TD]25/01/2016[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/01/2016[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]25/04/2016[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]30/10/2016[/TD]
[TD]29/01/2017[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]30/01/2017[/TD]
[TD]29/04/2017[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]30/04/2017[/TD]
[TD]29/07/2017[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]26/06/2015[/TD]
[TD]27/09/2015[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]28/09/2015[/TD]
[TD]27/12/2015[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]28/12/2015[/TD]
[TD]27/03/2016[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]28/03/2016[/TD]
[TD]27/05/2016[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]0004[/TD]
[TD]20/01/2015[/TD]
[TD]19/04/2015[/TD]
[TD]Q1[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, here is one option you can try:

Excel 2013/2016
	A	B	C	D
1	Ref	Start date	End Date	Qtr
2	0001	26/10/2015	25/01/2017	Q1
3	0001	26/01/2016	25/04/2017	Q2
4	0001	26/04/2016	25/07/2017	Q3
5	0001	26/07/2016	25/10/2017	Q4
6	0001	26/10/2015	25/01/2016	Q1
7	0001	26/01/2016	25/04/2016	Q2
8	0002	30/10/2016	29/01/2017	Q1
9	0002	30/01/2017	29/04/2017	Q2
10	0002	30/04/2017	29/07/2017	Q3
11	0003	26/06/2015	27/09/2015	Q1
12	0003	28/09/2015	27/12/2015	Q2
13	0003	28/12/2015	27/03/2016	Q3
14	0003	28/03/2016	27/05/2016	Q4
15	0004	20/01/2015	19/04/2015	Q1
Sheet1

Cell Formulas
Range	Formula
D2	=IF(A2<>A1,"Q1","Q"&IF(RIGHT(D1)="4","1",RIGHT(D1)+1))

 

[Vinoth_Shob]

Please use this formula - CONCATENATE("Q" & ROUNDUP(MONTH(C2)/3,0))

Column C2 refers to end month date

Hi,

I'm sure excel can do this but not sure where to start.

Please refer to the table below. Basically, I need a formula in the 'qtr' column which will reset to 'Q1' any time the value of the reference number in the 'ref' column changes.

Any help much appreciated! Thanks, Cal.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[TD]Qtr[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]26/10/2015[/TD]
[TD]25/01/2017[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/01/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25/04/2017[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/04/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25/07/2017[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/07/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25/10/2017[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]26/10/2015[/TD]
[TD]25/01/2016[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]26/01/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25/04/2016[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]30/10/2016[/TD]
[TD]29/01/2017[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]30/01/2017[/TD]
[TD]29/04/2017[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]30/04/2017[/TD]
[TD]29/07/2017[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]26/06/2015[/TD]
[TD]27/09/2015[/TD]
[TD]Q1[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]28/09/2015[/TD]
[TD]27/12/2015[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]28/12/2015[/TD]
[TD]27/03/2016[/TD]
[TD]Q3[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]28/03/2016[/TD]
[TD]27/05/2016[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]0004[/TD]
[TD]20/01/2015[/TD]
[TD]19/04/2015[/TD]
[TD]Q1[/TD]
[/TR]
</tbody>[/TABLE]