Ranking dates based off year to create unique transaction #

[dlmoore99]

Hello community, I've been racking my brain for a while now and need help. I'm trying to create unique transaction #'s based of the transaction date. Issue is that I need to start the transaction date over every year. I'm using Rank and Countifs functions combined to help create the Transaction ID but can't figure out how to start the numbering over for each year. Any help would be greatly appreciated.

Book1.xlsx
	A	B	C	D
1	Transaction Date	ID # w/duplicates	ID # w/o duplicates	What it should look like
2	12/15/17	2017-02	2017-02	2017-02
3	12/12/17	2017-01	2017-01	2017-01
4	12/19/17	2017-03	2017-03	2017-03
5	12/27/17	2017-04	2017-04	2017-04
6	02/09/18	2018-06	2018-06	2018-02
7	01/15/18	2018-05	2018-05	2018-01
8	03/22/18	2018-07	2018-07	2018-03
9	03/23/18	2018-08	2018-08	2018-04
10	03/23/18	2018-08	2018-09	2018-05
11	03/31/18	2018-010	2018-010	2018-06
12	09/17/18	2018-011	2018-011	2018-07
13	10/28/18	2018-012	2018-012	2018-08
14	11/03/18	2018-013	2018-013	2018-09
15	01/19/19	2019-014	2019-014	2019-01
16	02/07/19	2019-015	2019-015	2019-02
17	08/15/19	2019-016	2019-016	2019-03
18	10/04/19	2019-017	2019-017	2019-04
19	03/13/20	2020-018	2020-018	2020-01
20	03/13/20	2020-018	2020-019	2020-02
21
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=TEXT(A:A,"yyyy")&"-0"&RANK(A2,A$2:A$20,1)
C2:C20	C2	=TEXT(A:A,"yyyy")&"-0"&RANK(A2,A$2:A$1000,1)+COUNTIF($A$2:A2,A2)-1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20,D2:D20	Cell	contains a blank value	text	NO

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dlmoore99]

Sorry, info is updated

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A		C
1	Transaction Date		ID # w/o duplicates
2	15/12/2017		2017-02
3	12/12/2017		2017-01
4	19/12/2017		2017-03
5	27/12/2017		2017-04
6	09/02/2018		2018-02
7	15/01/2018		2018-01
8	22/03/2018		2018-03
9	23/03/2018		2018-04
10	23/03/2018		2018-05
11	31/03/2018		2018-06
12	17/09/2018		2018-07
13	28/10/2018		2018-08
14	03/11/2018		2018-09
15	19/01/2019		2019-01
16	07/02/2019		2019-02
17	15/08/2019		2019-03
18	04/10/2019		2019-04
19	13/03/2020		2020-01
20	13/03/2020		2020-02
21
Main

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(A2,"yyyy-")&TEXT(MATCH(A2,AGGREGATE(15,6,$A$2:$A$100/(YEAR($A$2:$A$100)=YEAR(A2)),ROW($A$2:$A$100)-ROW(A$2)+1),0)+COUNTIFS(A$2:A2,A2)-1,"00")

 

[dlmoore99]

Fluff, when I paste your formula into my spreadsheet, the cell returns #N/A, cell format in column C is General

 

[Marcelo Branco]

Maybe...

D2 copied down
=TEXT(A2,"yyyy")&"-"&TEXT(SUMPRODUCT(--(YEAR($A$2:$A$20)=YEAR(A2)),--($A$2:$A$20<A2))+COUNTIFS(A$2:A2,A2),"00")

M.

 

[Marcelo Branco]

Test

Pasta1
	A	B
1	Transaction Date	ID# w/o duplicates
2	12/15/2017	2017-02
3	12/12/2017	2017-01
4	12/19/2017	2017-03
5	12/27/2017	2017-04
6	02/09/2018	2018-02
7	01/15/2018	2018-01
8	03/22/2018	2018-03
9	03/23/2018	2018-04
10	03/23/2018	2018-05
11	03/31/2018	2018-06
12	09/17/2018	2018-07
13	10/28/2018	2018-08
14	11/03/2018	2018-09
15	01/19/2019	2019-01
16	02/07/2019	2019-02
17	08/15/2019	2019-03
18	10/04/2019	2019-04
19	03/13/2020	2020-01
20	03/13/2020	2020-02
dlmoore

Cell Formulas
Range	Formula
B2:B20	B2	=TEXT(A2,"aaaa")&"-"&TEXT(SUMPRODUCT(--(YEAR($A$2:$A$20)=YEAR(A2)),--($A$2:$A$20<A2))+COUNTIFS(A$2:A2,A2),"00")

Replace "aaaa" by "yyyy" in the formulas ("aaaa" = Portuguese)

Hope this helps

M.

 

[dlmoore99]

Marcelo, that formula worked, Thanks to both you and Fluff for your time and knowledge.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Marcelo Branco]

Marcelo, that formula worked, Thanks to both you and Fluff for your time and knowledge.

You are welcome. Glad to help.

M.