Mark combined if.......

[mark692]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Group Code[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]06/02/2017[/TD]
[TD]1[/TD]
[TD]combined[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]1[/TD]
[TD]combined[/TD]
[/TR]
[TR]
[TD]03/02/2018[/TD]
[TD]1[/TD]
[TD]combined[/TD]
[/TR]
[TR]
[TD]01/05/2017[/TD]
[TD]2[/TD]
[TD]nope[/TD]
[/TR]
[TR]
[TD]01/06/2017[/TD]
[TD]2[/TD]
[TD]nope[/TD]
[/TR]
[TR]
[TD]01/08/2018[/TD]
[TD]3[/TD]
[TD]combined[/TD]
[/TR]
[TR]
[TD]09/09/2017[/TD]
[TD]3[/TD]
[TD]combined[/TD]
[/TR]
</tbody>[/TABLE]


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
date	group code	remarks
nope
nope
nope
combined
combined
combined

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01/02/2017[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]02/02/2017[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]02/18/2018[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

</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