How to number the data based 2 set of rules (Date and Type)?

Question

I am creating a database where different numbers will be assigned to each row based on 2 parameters.

1. Date
2. Manager Name

As shown in the image based on the consultant and the date of booking, the booking id should change.

So e.g if there are 4 entries for ryan on 09/07/2020, the booking ids should be Ryan-09/07-1,Ryan-09/07-2,Ryan-09/07-3,Ryan-09/07-4 and for ryan's booking on 10/07/2020 the booking id should be Ryan-10/07-1 and so on.

I tried using countif but doesnt work. Any suggestions?

Answer 1

You can use a formula.

I used Table with structured references, but you can use regular addressing if you prefer.

With Structured referencing:

D2: =[@Consultant] & "-" & TEXT([@[Booking Date]],"dd/mm")  &"-" &COUNTIFS([@[Booking Date]]:INDEX([Booking Date],1),[@[Booking Date]],[@Consultant]:INDEX([Consultant],1),[@Consultant])

With regular addressing:

D2: =$C2 & "-" & TEXT($B2,"dd/mm")  &"-" &COUNTIFS($B2:INDEX($B:$B,2),$B2,$C2:INDEX($C:$C,2),$C2)

and fill down as far as needed

Answer 2

You need few Helper column to solve the issue:

How it works:

• Formula in cell AC 22:

  =MONTH(Y22)&Z22

• Formula in cell AB 22:

  =COUNTIF($AC$22:AC22,AC22)

• Final Formula in cell AA 22

  =Z22&"-"&TEXT(Y22,"mm/dd")&"-"&AB22

N.B.

• For neatness later on you may hide Helper Columnd.

• You may adjust cell references in the formula as needed.