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

 

[dlmoore99]

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.

awesome, thank you very much, but is there any way I can use this formula with wildcards instead of exact match, so instead of: IF(A2="Al M","Exempt",TEXT(B2,"yyyyy-")&TEXT(SUMPRODUCT(--(A$2:A$20<>"Al M"), I could use IF(A2="*Al*","Exempt",TEXT(B2,"yyyyy-")&TEXT(SUMPRODUCT(--(A$2:A$20<>"*Al*"), I tried it and it doesn't work

 

[Marcelo Branco]

This would be an error-prone solution, as it would eliminate any name that contained "Al M". For a robust solution, I recommend creating a unique numeric code for each consumer. That done, it would be easy to create a formula that excludes one or more consumers.

M.

 

[dlmoore99]

This would be an error-prone solution, as it would eliminate any name that contained "Al M". For a robust solution, I recommend creating a unique numeric code for each consumer. That done, it would be easy to create a formula that excludes one or more consumers.

M.

Ok, sounds good, not quite sure how to do that, but I'll look into it and see what I can do on my own before asking for help. Thanks again.

 

[dlmoore99]

Ok, sounds good, not quite sure how to do that, but I'll look into it and see what I can do on my own before asking for help. Thanks again.

If I can't use wildcards, can I have more than one name that is Exempt in the equation, if yes how so, I've tried using if/or to no avail and a few other arguments, I've also tried ISNUMBER(SEARCH) but it won't work properly either

 

[Marcelo Branco]

An example of a possible robust solution
1. Create a table Costumer - Code (H2:I15)
2. Exempt list (codes) in F2:F10 (gray area)

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

Cell Formulas
Range	Formula
B2:B20	B2	=VLOOKUP(A2,$H$2:$I$15,2,0)
D2:D20	D2	=IF(A2="","",IF(ISNUMBER(MATCH(B2,F$2:F$10,0)),"Exempt",TEXT(C2,"aaaa-")&TEXT(SUMPRODUCT(--(YEAR(C$2:C$20)=YEAR(C2)),--ISNA(MATCH(B$2:B$20,F$2:F$10,0)),--(C$2:C$20&"|"&B$2:B$20<C2&"|"&B2),--(MATCH(C$2:C$20&"|"&B$2:B$20,C$2:C$20&"|"&B$2:B$20,0)=ROW(C$2:C$20)-ROW(C$2)+1))+1,"00")))

Replace "aaaa" by "yyyy" in the formulas in column D

M.

 

[dlmoore99]

An example of a possible robust solution
1. Create a table Costumer - Code (H2:I15)
2. Exempt list (codes) in F2:F10 (gray area)

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

Cell Formulas
Range	Formula
B2:B20	B2	=VLOOKUP(A2,$H$2:$I$15,2,0)
D2:D20	D2	=IF(A2="","",IF(ISNUMBER(MATCH(B2,F$2:F$10,0)),"Exempt",TEXT(C2,"aaaa-")&TEXT(SUMPRODUCT(--(YEAR(C$2:C$20)=YEAR(C2)),--ISNA(MATCH(B$2:B$20,F$2:F$10,0)),--(C$2:C$20&"|"&B$2:B$20<C2&"|"&B2),--(MATCH(C$2:C$20&"|"&B$2:B$20,C$2:C$20&"|"&B$2:B$20,0)=ROW(C$2:C$20)-ROW(C$2)+1))+1,"00")))

Replace "aaaa" by "yyyy" in the formulas in column D

M.

Ok, I'll give this a try, will have to make hundreds of customer codes as I was posting a snap shot of a much bigger sheet, and yes i remember needing to change the "aaaa" (Portuguese)

 

[dlmoore99]

Ok, I'll give this a try, will have to make hundreds of customer codes as I was posting a snap shot of a much bigger sheet, and yes i remember needing to change the "aaaa" (Portuguese)

Ok I tried your new formula and get #N/A for all cells except the "Exempt" cells. What does "|" do in your formula?

 

[Marcelo Branco]

The formula worked perfectly for me (see post #15)
"|" is used to separate Dates from Codes (to make the formula safer)

M.

 

[dlmoore99]

The formula worked perfectly for me (see post #15)
"|" is used to separate Dates from Codes (to make the formula safer)

M.

Thanks for you all your help and time. I finally got it to work, found a name that wasn't exact in both "A" & "H" columns on my actual sheet that was causing a #N/A in the Code which in turned caused a #N/A in the ranking, one last thing if possible, I have 3 different types of Exempts based off the type of customer, is there anyway to incorporate the types of Exempts into the "Ranking" aka Unique Transaction ID. I was able to add the year to the Exempt on your coding/formula but can't figure out to add the 3 types of Exempts to your formula. I was trying to use this code previously:

Book1.xlsx
	A	B	C	D
1	Customer	Transaction Date	9th+ try	What it should look like
2	Al M	12/15/17	2017-02	2017-02
3	Bob S	12/12/17	2017-01	2017-01
4	Joe D -	12/19/17	EXEMPT 2017 BUS.	EXEMPT 2017 BUS.
5	Carl C	12/27/17	2017-03	2017-03
6	Andy L	02/09/18	2018-02	2018-02
7	Will T	01/15/18	2018-01	2018-01
8	Tom G PD	03/22/18	EXEMPT 2018 MUNICIPAL	EXEMPT 2018 MUNICIPAL
9	Joe D	03/23/18	2018-05	2018-04
10	Joe D	03/23/18	2018-05	2018-04
11	Bob S	03/23/18	2018-04	2018-03
12	Fred G	09/17/18	2018-06	2018-05
13	Dana M	10/28/18	EXEMPT 2018 OWNER	EXEMPT 2018 OWNER
14	Scott F	11/03/18	2018-08	2018-06
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(ISNUMBER(SEARCH("-",A2)),"EXEMPT"&CHAR(10)&TEXT(B2,"yyyy ")&" BUS.",IF(ISNUMBER(SEARCH("PD",A2)),"EXEMPT"&CHAR(10)&TEXT(B2,"yyyy")&" MUNICIPAL",IF(A2="DANA M","EXEMPT"&CHAR(10)&TEXT(B2,"yyyy")&" OWNER",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"))))

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

 

[Marcelo Branco]

I'm afraid i can't help you anymore. As i said it's important to create a numeric code for each customer and a Exempt list. I can't see anything like this in your last worksheet. Beside that, you changed the requirements, once more time, and i didn't understand what you're trying to do this time.
I hope you can adapt the formula i provided and got a workable solution.

M.