Mark combined if.......

[mark692]

Date	Group Code	Remarks
06/02/2017	1	combined
01/01/2018	1	combined
03/02/2018	1	combined
01/05/2017	2	nope
01/06/2017	2	nope
01/08/2018	3	combined
09/09/2017	3	combined

<tbody>
</tbody>

hi guys is this possible,

if the year of dates within the same group is different i want to put "combined" in remarks column, if not put "nope"?

 

[FormR]

Hi, here is one option that you can try:

Excel 2013/2016
	A	B	C
1	Date	Group Code	Remarks
2	06/02/2017	1	Combined
3	01/01/2018	1	Combined
4	03/02/2018	1	Combined
5	01/05/2017	2	Nope
6	01/06/2017	2	Nope
7	01/08/2018	3	Combined
8	09/09/2017	3	Combined
Sheet1

Cell Formulas
Range	Formula
C2	=IF(COUNTIFS($A$2:$A$8,">="&DATE(YEAR(A2),1,1),$A$2:$A$8,"<="&DATE(YEAR(A2),12,31),$B$2:$B$8,B2)=COUNTIFS($B$2:$B$8,B2),"Nope","Combined")

 

[mark692]

thanks sir your formula work but the problem occur when table is like this


Excel 2013/2016

	A	B	C
1	date	group code	remarks
2	01/01/2017	1	nope
3	01/02/2017	1	nope
4		1	nope
5	02/02/2017	2	combined
6	02/18/2018	2	combined
7		2	combined

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

a cell on column A has no data but within group code

 

[FormR]

problem occur when table is like this

Hi, I guess you you forgot to tell us that was a possibility, here is one option - pay attention to the array formula notes.

Excel 2013/2016
	A	B	C
1	Date	Group Code	Remarks
2	01/01/2017	1	Nope
3	01/02/2017	1	Nope
4		1	Nope
5	01/05/2017	2	Combined
6	01/06/2018	2	Combined
7		2	Combined
Sheet1

Cell Formulas
Range	Formula
C2	{=IF(SUM(0+(FREQUENCY(IF($B$2:$B$7=B2,IF(ISNUMBER($A$2:$A$7),YEAR($A$2:$A$7))),YEAR($A$2:$A$7))>0))=1,"Nope","Combined")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mark692]

thanks again sir your array formula work but can i add another column that will show me the year? like this

Excel 2013/2016
	A	B	C	D
1	Date	Group Code	Remarks	Year
2	01/01/2017	1	Nope	2017
3	01/02/2017	1	Nope	2017
4		1	Nope	2017
5	01/05/2017	2	Combined	2017, 2018
6	01/06/2018	2	Combined	2017, 2018
7		2	Combined	2017, 2018
Sheet1

 

[FormR]

can i add another column that will show me the year?

Hi, I don't think it will be easy to list the years in the same cell without using VBA of some sort, maybe in the form of a UDF or we could probably list the years in seperate cells (like below) if VBA is not option.

What are your thoughts?

Excel 2013/2016
	A	B	C	D	E	F
1	Date	Group Code	Remarks	Years..
2	01/01/2017	1	Nope	2017
3	01/02/2017	1	Nope	2017
4		1	Nope	2017
5	01/05/2017	2	Combined	2017	2018	2019
6	01/06/2018	2	Combined	2017	2018	2019
7	01/06/2016	2	Combined	2017	2018	2019
8		2	Combined	2017	2018	2019
Sheet1