Ranking dates based off year to create unique transaction #

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
72
Office Version
  1. 2019
Platform
  1. MacOS
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
ABCD
1CustomerTransaction DateCurrent FormulaWhat it should look like
2Bob S12/15/172017-022017-02
3Bob S12/12/172017-012017-01
4Joe D12/19/172017-032017-03
5Carl C12/27/172017-042017-04
6Andy L02/09/182018-022018-02
7Will T01/15/182018-012018-01
8Tom G03/22/182018-032018-03
9Joe D03/23/182018-042018-04
10Joe D03/23/182018-052018-04
11Bob S03/31/182018-062018-05
12Fred G09/17/182018-072018-06
13Al M10/28/182018-082018-07
14Scott F11/03/182018-092018-08
15Mark W01/19/192019-012019-01
16Brett B02/07/192019-022019-02
17Marcus J08/15/192019-032019-03
18Robert M10/04/192019-042019-04
19Charles H03/13/202020-012020-01
20Charles H03/13/202020-022020-01
Sheet1
Cell Formulas
RangeFormula
C2:C20C2=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
CellConditionCell FormatStop If True
B2:B20,D2:D20Cellcontains a blank value textNO
 

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
72
Office Version
  1. 2019
Platform
  1. MacOS
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 20, 2011
Messages
72
Office Version
  1. 2019
Platform
  1. MacOS
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

Board Regular
Joined
May 20, 2011
Messages
72
Office Version
  1. 2019
Platform
  1. MacOS
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHI
1CustomerCodeTransaction DateRankingExempt ListCustomerCode
2Bob S312/15/20172017-021Al M1
3Bob S312/12/20172017-014Andy L2
4Joe D812/19/20172017-035Bob S3
5Carl C512/27/2017ExemptBrett B4
6Andy L202/09/20182018-02Carl C5
7Will T1401/15/20182018-01Charles H6
8Tom G1303/22/20182018-03Fred G7
9Joe D803/23/20182018-05Joe D8
10Joe D803/23/20182018-05Marcus J9
11Bob S303/23/20182018-04Mark W10
12Fred G709/17/20182018-06Robert M11
13Al M110/28/2018ExemptScott F12
14Scott F1211/03/20182018-07Tom G13
15Mark W1001/19/20192019-01Will T14
16Brett B402/07/2019Exempt
17Marcus J908/15/20192019-02
18Robert M1110/04/20192019-03
19Charles H603/13/20202020-01
20Charles H603/13/20202020-01
dlmoore
Cell Formulas
RangeFormula
B2:B20B2=VLOOKUP(A2,$H$2:$I$15,2,0)
D2:D20D2=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.
 
Solution

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
72
Office Version
  1. 2019
Platform
  1. MacOS
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
ABCDEFGHI
1CustomerCodeTransaction DateRankingExempt ListCustomerCode
2Bob S312/15/20172017-021Al M1
3Bob S312/12/20172017-014Andy L2
4Joe D812/19/20172017-035Bob S3
5Carl C512/27/2017ExemptBrett B4
6Andy L202/09/20182018-02Carl C5
7Will T1401/15/20182018-01Charles H6
8Tom G1303/22/20182018-03Fred G7
9Joe D803/23/20182018-05Joe D8
10Joe D803/23/20182018-05Marcus J9
11Bob S303/23/20182018-04Mark W10
12Fred G709/17/20182018-06Robert M11
13Al M110/28/2018ExemptScott F12
14Scott F1211/03/20182018-07Tom G13
15Mark W1001/19/20192019-01Will T14
16Brett B402/07/2019Exempt
17Marcus J908/15/20192019-02
18Robert M1110/04/20192019-03
19Charles H603/13/20202020-01
20Charles H603/13/20202020-01
dlmoore
Cell Formulas
RangeFormula
B2:B20B2=VLOOKUP(A2,$H$2:$I$15,2,0)
D2:D20D2=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

Board Regular
Joined
May 20, 2011
Messages
72
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
The formula worked perfectly for me (see post #15)
"|" is used to separate Dates from Codes (to make the formula safer)

M.
 

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
72
Office Version
  1. 2019
Platform
  1. MacOS
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
ABCD
1CustomerTransaction Date9th+ try What it should look like
2Al M12/15/172017-022017-02
3Bob S12/12/172017-012017-01
4Joe D -12/19/17EXEMPT 2017 BUS.EXEMPT 2017 BUS.
5Carl C12/27/172017-032017-03
6Andy L02/09/182018-022018-02
7Will T01/15/182018-012018-01
8Tom G PD03/22/18EXEMPT 2018 MUNICIPALEXEMPT 2018 MUNICIPAL
9Joe D03/23/182018-052018-04
10Joe D03/23/182018-052018-04
11Bob S03/23/182018-042018-03
12Fred G09/17/182018-062018-05
13Dana M10/28/18EXEMPT 2018 OWNEREXEMPT 2018 OWNER
14Scott F11/03/182018-082018-06
15Mark W01/19/192019-012019-01
16Brett B02/07/192019-022019-02
17Marcus J08/15/192019-032019-03
18Robert M10/04/192019-042019-04
19Charles H03/13/202020-022020-02
20Brett B03/13/202020-012020-01
Sheet1
Cell Formulas
RangeFormula
C2:C20C2=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
CellConditionCell FormatStop If True
B2:B20,D2:D20Cellcontains a blank value textNO
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,526
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top