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 |