year of the date

[mark692]

i want to put a formula on column C that will show me the year of the given date on column A inside the same group code in column B, like this

Excel 2013/2016
	A	B	C
1	Date	Group Code	Year
2	03/17/2018	1	2018
3		1	2018
4		1	2018
5	03/06/2017	2	2016, 2017
6	01/01/2016	2	2016, 2017
7		2	2016, 2017
8	12/01/2017	3	2017
9		3	2017
Sheet1

 

[Rick Rothstein]

Does each code group always occupy exactly 3 rows as shown in your example?

 

[mark692]

Does each code group always occupy exactly 3 rows as shown in your example?

no sir group code number 3 has only 2 rows

 

[Rick Rothstein]

See if this UDF (user defined function) does what you want...

Function GroupDates(GroupNum As Long) As String
  Dim R As Long, Dates As Variant
  Dates = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
  For R = UBound(Dates) To 1 Step -1
    If Cells(R, "B").Value = GroupNum And Cells(R, "A") <> "" Then
      GroupDates = GroupDates & ", " & Year(Cells(R, "A").Value)
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GroupDates just like it was a built-in Excel function. For example,

=GroupDates(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Nishant94]

If you have Excel 2016, you can use this or UDF is the way to go for you as given by Rick Rothstein :

Unknown
	A	B	C
1	Date	Group Code	Year
2	03/17/2018	1	2018
3		1	2018
4		1	2018
5	03/06/2017	2	2017,2016
6	01/01/2016	2	2017,2016
7		2	2017,2016
8	12/01/2017	3	2017
9		3	2017
10
Sheet13

Cell Formulas
Range	Formula
C2	{=TEXTJOIN(",",,IF(($B$2:$B$9=B2)*($A$2:$A$9<>""),YEAR($A$2:$A$9),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mark692]

If you have Excel 2016, you can use this or UDF is the way to go for you as given by Rick Rothstein :

Unknown

	A	B	C
Date	Group Code	Year

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]03/17/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]

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

[TD="align: center"]5[/TD]
[TD="align: right"]03/06/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2017,2016[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/01/2016[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2017,2016[/TD]

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

[TD="align: center"]8[/TD]
[TD="align: right"]12/01/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2017[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2017[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]{=TEXTJOIN(",",,IF(($B$2:$B$9=B2)*($A$2:$A$9<>""),YEAR($A$2:$A$9),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Enter the formula with "Ctrl+Shift+Enter". Only Pressing "Enter" will result in formula giving error or incorrect result. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Hi im using my your formula bat it gives me result like this yes im using 2016 thanks

	A	B	C
date	group	year

<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/21/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]#NAME?[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]01/21/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]#NAME?[/TD]

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

[TD="align: center"]5[/TD]
[TD="align: right"]02/21/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]#NAME?[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]02/21/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]#NAME?[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=TEXTJOIN(",",,IF(($B$2:$B$6=B2)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]{=TEXTJOIN(",",,IF(($B$2:$B$6=B3)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]{=TEXTJOIN(",",,IF(($B$2:$B$6=B4)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=TEXTJOIN(",",,IF(($B$2:$B$6=B5)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]{=TEXTJOIN(",",,IF(($B$2:$B$6=B6)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[mark692]

See if this UDF (user defined function) does what you want...

Function GroupDates(GroupNum As Long) As String
  Dim R As Long, Dates As Variant
  Dates = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
  For R = UBound(Dates) To 1 Step -1
    If Cells(R, "B").Value = GroupNum And Cells(R, "A") <> "" Then
      GroupDates = GroupDates & ", " & Year(Cells(R, "A").Value)
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GroupDates just like it was a built-in Excel function. For example,

=GroupDates(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

hello sir your formula works but when i tried to apply it on may real working workbook it gives me error

 

[Rick Rothstein]

hello sir your formula works but when i tried to apply it on may real working workbook it gives me error

What was the error number and error description that it gave you?

 

[mark692]

like this sir,

[/URL][/IMG]


its gives me 2016,2016,2015,2015,2015

instead of 2016,2015 only

the group code is the column that colored yellow

 

[Dossfm0q]

maybe you need to add: Application.Volatile before last Line for update Auto or Use F9

GroupDates = Mid(GroupDates, 3)
Application.Volatile
End Function