Finding the Unique value, and then find the corresponding year

[Goddess]

/Hi,

I have a table with various text and a date corresponding to the text. I need to find the unique value in column A and then return the year. Anyone can help?

Book1
	A	B	C	D	E	F
1	Data			Result
2	Apple	5 Nov 2014		Apple	2014
3	Grape	10 Nov 2014			2017
4	Apple	20 Nov 2014			2018
5	Orange	15 Dec 2014		Grape	2014
6	Apple	10 Aug 2017			2018
7	Apple	20 Sept 2017		Orange	2014
8	Orange	20 Sept 2017			2017
9	Grape	15 Jan 2018			2018
10	Grape	15 Jan 2018
11	Orange	05 Feb 2018
12	Orange	05 Feb 2018
13	Apple	5 Feb 2018
14
Sheet1

Thanks!

 

[Peter_SSs]

Using formulas you would not be able to have those merged cells. Would this suffice or do you need a vba solution?

25 03 28.xlsm
	A	B	C	D	E
1	Data
2	Apple	5 Nov 2014		Apple	2014
3	Grape	10 Nov 2014		Apple	2017
4	Apple	20 Nov 2014		Apple	2018
5	Orange	15 Dec 2014		Grape	2014
6	Apple	10 Aug 2017		Grape	2018
7	Apple	20 Sep 2017		Orange	2014
8	Orange	20 Sep 2017		Orange	2017
9	Grape	15 Jan 2018		Orange	2018
10	Grape	15 Jan 2018
11	Orange	5 Feb 2018
12	Orange	5 Feb 2018
13	Apple	5 Feb 2018
Goddess

Cell Formulas
Range	Formula
D2:D9	D2	=LET(a,A2:A13,y,YEAR(B2:B13),x,UNIQUE(SORTBY(a&"|"&y,a)),LEFT(x,FIND("|",x)-1))
E2:E9	E2	=LET(a,A2:A13,y,YEAR(B2:B13),x,UNIQUE(SORTBY(a&"|"&y,a)),REPLACE(x,1,FIND("|",x),""))
Dynamic array formulas.

 

[footoo]

A bit shorter :

In D2 : =SORT(TEXTBEFORE(UNIQUE(A2:A13 & YEAR(B2:B13)),2))

In E2 : =RIGHT(SORT(UNIQUE(A2:A13&YEAR(B2:B13))),4)

 

[footoo]

Can do only in D2 : =LET(a,UNIQUE(A2:A13&YEAR(B2:B13)),HSTACK(SORT(TEXTBEFORE(a,2)),RIGHT(SORT(a),4)))

 

[Goddess]

Thanks both for the super prompt replies! Appreciate it as always...

@Peter : Will try and see if I can work without merging the cells. No VBA for me as I'm already not very good in Excel, VBA will kill me

 

[footoo]

A bit shorter :

=LET(a,SORT(UNIQUE(A2:A13&YEAR(B2:B13))),HSTACK(TEXTBEFORE(a,2),RIGHT(a,4)))

 

[Alex Blakenburg]

@footoo, the OP's profile shows Excel version 2021, this version doesn't have access to HStack or TextBefore.

 

[footoo]

@footoo, the OP's profile shows Excel version 2021, this version doesn't have access to HStack or TextBefore.

Oops !

 

[footoo]

@footoo, the OP's profile shows Excel version 2021, this version doesn't have access to HStack or TextBefore.

Revised :

In D2 : =LET(a,SORT(UNIQUE(A2:A13&YEAR(B2:B13))),LEFT(a,LEN(a)-4))

In E2 : =RIGHT(SORT(UNIQUE(A2:A13&YEAR(B2:B13))),4)