Ranking dates based off year to create unique transaction #

[dlmoore99]

New twist to previous answered thread. I'm now trying to figure out how to rank entries based off name and date. So if the same name appears more than once in one column and same the date in another column matches, create a duplicate unique id, if not continue the unique ID numbering.

Book1.xlsx
	A	B	C	D
1	Customer	Transaction Date	Current Formula	What it should look like
2	Bob S	12/15/17	2017-02	2017-02
3	Bob S	12/12/17	2017-01	2017-01
4	Joe D	12/19/17	2017-03	2017-03
5	Carl C	12/27/17	2017-04	2017-04
6	Andy L	02/09/18	2018-02	2018-02
7	Will T	01/15/18	2018-01	2018-01
8	Tom G	03/22/18	2018-03	2018-03
9	Joe D	03/23/18	2018-04	2018-04
10	Joe D	03/23/18	2018-05	2018-04
11	Bob S	03/31/18	2018-06	2018-05
12	Fred G	09/17/18	2018-07	2018-06
13	Al M	10/28/18	2018-08	2018-07
14	Scott F	11/03/18	2018-09	2018-08
15	Mark W	01/19/19	2019-01	2019-01
16	Brett B	02/07/19	2019-02	2019-02
17	Marcus J	08/15/19	2019-03	2019-03
18	Robert M	10/04/19	2019-04	2019-04
19	Charles H	03/13/20	2020-01	2020-01
20	Charles H	03/13/20	2020-02	2020-01
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(B2,"yyyy-")&TEXT(SUMPRODUCT(--(YEAR($B$2:$B$20)=YEAR(B2)),--($B$2:$B$20<B2))+COUNTIFS(B$2:B2,B2),"00")

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

 

[Peter_SSs]

Try this. In your version it may need confirming with Ctrl+Shift+Enter, not just Enter.

21 03 13.xlsm
	B	C	D
1	Transaction Date	Current Formula	What it should look like
2	15/12/2017	2017-02	2017-02
3	12/12/2017	2017-01	2017-01
4	19/12/2017	2017-03	2017-03
5	27/12/2017	2017-04	2017-04
6	9/02/2018	2018-02	2018-02
7	15/01/2018	2018-01	2018-01
8	22/03/2018	2018-03	2018-03
9	23/03/2018	2018-04	2018-04
10	23/03/2018	2018-05	2018-04
11	31/03/2018	2018-06	2018-05
12	17/09/2018	2018-07	2018-06
13	28/10/2018	2018-08	2018-07
14	3/11/2018	2018-09	2018-08
15	19/01/2019	2019-01	2019-01
16	7/02/2019	2019-02	2019-02
17	15/08/2019	2019-03	2019-03
18	4/10/2019	2019-04	2019-04
19	13/03/2020	2020-01	2020-01
20	13/03/2020	2020-02	2020-01
Trans #

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(B2,"yyyy-")&TEXT(SUMPRODUCT(--(YEAR($B$2:$B$20)=YEAR(B2)),--($B$2:$B$20<B2))+COUNTIFS(B$2:B2,B2),"00")
D2:D20	D2	=TEXT(B2,"yyyy-")&TEXT(SUMPRODUCT(--(YEAR($B$2:$B$20)=YEAR(B2)),--($B$2:$B$20<B2),--(ROW($B$2:$B$20)-ROW($B$2)+1=MATCH($B$2:$B$20,$B$2:$B$20,0)))+1,"00")

 

[dlmoore99]

Try this. In your version it may need confirming with Ctrl+Shift+Enter, not just Enter.

21 03 13.xlsm
	B	C	D
1	Transaction Date	Current Formula	What it should look like
2	15/12/2017	2017-02	2017-02
3	12/12/2017	2017-01	2017-01
4	19/12/2017	2017-03	2017-03
5	27/12/2017	2017-04	2017-04
6	9/02/2018	2018-02	2018-02
7	15/01/2018	2018-01	2018-01
8	22/03/2018	2018-03	2018-03
9	23/03/2018	2018-04	2018-04
10	23/03/2018	2018-05	2018-04
11	31/03/2018	2018-06	2018-05
12	17/09/2018	2018-07	2018-06
13	28/10/2018	2018-08	2018-07
14	3/11/2018	2018-09	2018-08
15	19/01/2019	2019-01	2019-01
16	7/02/2019	2019-02	2019-02
17	15/08/2019	2019-03	2019-03
18	4/10/2019	2019-04	2019-04
19	13/03/2020	2020-01	2020-01
20	13/03/2020	2020-02	2020-01
Trans #

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(B2,"yyyy-")&TEXT(SUMPRODUCT(--(YEAR($B$2:$B$20)=YEAR(B2)),--($B$2:$B$20<B2))+COUNTIFS(B$2:B2,B2),"00")
D2:D20	D2	=TEXT(B2,"yyyy-")&TEXT(SUMPRODUCT(--(YEAR($B$2:$B$20)=YEAR(B2)),--($B$2:$B$20<B2),--(ROW($B$2:$B$20)-ROW($B$2)+1=MATCH($B$2:$B$20,$B$2:$B$20,0)))+1,"00")

That won't work if I have a different customer transaction on the same day.

Book1.xlsx
	A	B	C	D
1	Customer	Transaction Date	Current Formula	What it should look like
2	Bob S	12/15/17	2017-02	2017-02
3	Bob S	12/12/17	2017-01	2017-01
4	Joe D	12/19/17	2017-03	2017-03
5	Carl C	12/27/17	2017-04	2017-04
6	Andy L	02/09/18	2018-02	2018-02
7	Will T	01/15/18	2018-01	2018-01
8	Tom G	03/22/18	2018-03	2018-03
9	Joe D	03/23/18	2018-04	2018-04
10	Joe D	03/23/18	2018-04	2018-04
11	Bob S	03/23/18	2018-04	2018-05
12	Fred G	09/17/18	2018-05	2018-06
13	Al M	10/28/18	2018-06	2018-07
14	Scott F	11/03/18	2018-07	2018-08
15	Mark W	01/19/19	2019-01	2019-01
16	Brett B	02/07/19	2019-02	2019-02
17	Marcus J	08/15/19	2019-03	2019-03
18	Robert M	10/04/19	2019-04	2019-04
19	Charles H	03/13/20	2020-01	2020-01
20	Charles H	03/13/20	2020-01	2020-01
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(B2,"yyyy-")&TEXT(SUMPRODUCT(--(YEAR($B$2:$B$20)=YEAR(B2)),--($B$2:$B$20<B2),--(ROW($B$2:$B$20)-ROW($B$2)+1=MATCH($B$2:$B$20,$B$2:$B$20,0)))+1,"00")

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

 

[kvsrinivasamurthy]

This ARRAY formula in C2 then copied down also works.

=TEXT(B2,"yyyy-")&TEXT(1+SUM(IFERROR(1/((YEAR($B$2:$B$20)=YEAR(B2))*COUNTIFS(B2,">"&B$2:$B$20)*COUNTIFS(B$2:$B$20,B$2:$B$20)),0)),"00")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[dlmoore99]

This ARRAY formula in C2 then copied down also works.

=TEXT(B2,"yyyy-")&TEXT(1+SUM(IFERROR(1/((YEAR($B$2:$B$20)=YEAR(B2))*COUNTIFS(B2,">"&B$2:$B$20)*COUNTIFS(B$2:$B$20,B$2:$B$20)),0)),"00")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

I still get the same issue, when there is a transaction on the same date, it counts all transactions as the same and get the same ID # and doesn't account for a different name when on the same date. So I need the transaction to account for the name as well as the date and not just date.

 

[Marcelo Branco]

Maybe...

Pasta1
	A	B	C
1	Customer	Transaction Date	Formula
2	Bob S	12/15/2017	2017-02
3	Bob S	12/12/2017	2017-01
4	Joe D	12/19/2017	2017-03
5	Carl C	12/27/2017	2017-04
6	Andy L	02/09/2018	2018-02
7	Will T	01/15/2018	2018-01
8	Tom G	03/22/2018	2018-03
9	Joe D	03/23/2018	2018-05
10	Joe D	03/23/2018	2018-05
11	Bob S	03/23/2018	2018-04
12	Fred G	09/17/2018	2018-06
13	Al M	10/28/2018	2018-07
14	Scott F	11/03/2018	2018-08
15	Mark W	01/19/2019	2019-01
16	Brett B	02/07/2019	2019-02
17	Marcus J	08/15/2019	2019-03
18	Robert M	10/04/2019	2019-04
19	Charles H	03/13/2020	2020-01
20	Charles H	03/13/2020	2020-01
Planilha5

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(B2,"aaaa")&"-"&TEXT(SUMPRODUCT(--(YEAR(B$2:B$20)=YEAR(B2)),--(B$2:B$20&A$2:A$20<B2&A2),--(MATCH(A$2:A$20&B$2:B$20,A$2:A$20&B$2:B$20,0)=ROW(B$2:B$20)-ROW(B$2)+1))+1,"00")

Note that on 03/23/2018 Bob S received a lower ranking than Joe D, because, alphabetically, Bob is lower than Joe.

M.

 

[dlmoore99]

Maybe...

Pasta1
	A	B	C
1	Customer	Transaction Date	Formula
2	Bob S	12/15/2017	2017-02
3	Bob S	12/12/2017	2017-01
4	Joe D	12/19/2017	2017-03
5	Carl C	12/27/2017	2017-04
6	Andy L	02/09/2018	2018-02
7	Will T	01/15/2018	2018-01
8	Tom G	03/22/2018	2018-03
9	Joe D	03/23/2018	2018-05
10	Joe D	03/23/2018	2018-05
11	Bob S	03/23/2018	2018-04
12	Fred G	09/17/2018	2018-06
13	Al M	10/28/2018	2018-07
14	Scott F	11/03/2018	2018-08
15	Mark W	01/19/2019	2019-01
16	Brett B	02/07/2019	2019-02
17	Marcus J	08/15/2019	2019-03
18	Robert M	10/04/2019	2019-04
19	Charles H	03/13/2020	2020-01
20	Charles H	03/13/2020	2020-01
Planilha5

Cell Formulas
Range	Formula
C2:C20	C2	=TEXT(B2,"aaaa")&"-"&TEXT(SUMPRODUCT(--(YEAR(B$2:B$20)=YEAR(B2)),--(B$2:B$20&A$2:A$20<B2&A2),--(MATCH(A$2:A$20&B$2:B$20,A$2:A$20&B$2:B$20,0)=ROW(B$2:B$20)-ROW(B$2)+1))+1,"00")

Note that on 03/23/2018 Bob S received a lower ranking than Joe D, because, alphabetically, Bob is lower than Joe.

M.

Marcelo, thanks for that formula, works great. Is there any way to make it say "Exempt" for the Transaction ID if there is a certain name in the list and it will skip numbering that cell and continue numbering in the next cell?

 

[Marcelo Branco]

Marcelo, thanks for that formula, works great. Is there any way to make it say "Exempt" for the Transaction ID if there is a certain name in the list and it will skip numbering that cell and continue numbering in the next cell?

I didn't quite understand what you want now - maybe an example?

M.

 

[dlmoore99]

I didn't quite understand what you want now - maybe an example?

M.

so if the name Al was in column A then the unique ID would be "Exempt" and not get a unique Transaction # and the unique transaction numbering would skip the Exempt and go one to the next row.

Book1.xlsx
	A	B	C	D
1	Customer	Transaction Date	Marcelo's Formula	What it should look like
2	Bob S	12/15/17	2017-02	2017-02
3	Bob S	12/12/17	2017-01	2017-01
4	Joe D	12/19/17	2017-03	2017-03
5	Carl C	12/27/17	2017-04	2017-04
6	Andy L	02/09/18	2018-02	2018-02
7	Will T	01/15/18	2018-01	2018-01
8	Tom G	03/22/18	2018-03	2018-03
9	Joe D	03/23/18	2018-05	2018-05
10	Joe D	03/23/18	2018-05	2018-04
11	Bob S	03/23/18	2018-04	2018-04
12	Fred G	09/17/18	2018-06	2018-06
13	Al M	10/28/18	2018-07	Exempt
14	Scott F	11/03/18	2018-08	2018-07
15	Mark W	01/19/19	2019-01	2019-01
16	Brett B	02/07/19	2019-02	2019-02
17	Marcus J	08/15/19	2019-03	2019-03
18	Robert M	10/04/19	2019-04	2019-04
19	Charles H	03/13/20	2020-02	2020-02
20	Brett B	03/13/20	2020-01	2020-01
Sheet1

Cell Formulas
Range	Formula
C2:C20	C2	=IF(A2="","",TEXT(B2,"yyyy-")&TEXT(SUMPRODUCT(--(YEAR(B$2:B$20)=YEAR(B2)),--(B$2:B$20&A$2:A$20<B2&A2),--(MATCH(A$2:A$20&B$2:B$20,A$2:A$20&B$2:B$20,0)=ROW(B$2:B$20)-ROW(B$2)+1))+1,"00"))

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

 

[Marcelo Branco]

Try

=IF(A2="","",IF(A2="Al M","Exempt",TEXT(B2,"yyyyy-")&TEXT(SUMPRODUCT(--(A$2:A$20<>"Al M"),--(YEAR(B$2:B$20)=YEAR(B2)),--(B$2:B$20&A$2:A$20<B2&A2),--(MATCH(A$2:A$20&B$2:B$20,A$2:A$20&B$2:B$20,0)=ROW(B$2:B$20)-ROW(B$2)+1))+1,"00")))

M.