Unique Report Number Using Formula

[Kevin Rush]

Hi,

I have a formula that creates a unique report number. The formula looks at the location, the type of job and also the year the work was carried out. Based on this a number is given (please note the report number also contains letters).

This works great except when you have 2 job at the same location in the same year as the number are the same. I wold like the number to ascend accordingly.

Below is my formula the last digit (1) is what I would like to increase but only of there is already another job/jobs that match.

Please Login or Register  to view this content.

I have also attached the workbook in case I haven't explained this well enough. In column A the two yellow cell are the same as the location, job type and date match, this in an example of where i would like the next one down to increase by 1 .

Also, i have tried to make the number 3 digits minimum (001,002 etc) but I cant get that to work either.

Thanks for your time.

[6StringJazzer]

Updated formula in attachment. This counts how many combinations of location/year there are for each job and assigns the appropriate number.

However, remember that with any formula solution, the results are going to get scrambled if you sort this table, or delete or insert rows in the middle of the table. If you need the number to be assigned once and stick no matter what else you do, you will have to use VBA.

[kvsrinivasamurthy]

Try this in A2. Sorting of data do not affect the formula result. Try if required.

Please Login or Register  to view this content.

[Gregb11]

Just BTW @kvsrinivasamurthy, sorting of data DOES affect the formula result. Change Date in E3 to 7/1/2021 and then sort by date (Newest to oldest). Prior to sorting, the suffix in A1 is 001. After sorting it changes to 002. Like 6StringJazzer says, I don't think there's any formula solution that will keep the value from changing upon sorting.

[Kevin Rush]

Thanks, that worked perfectly.

As for sorting the data, that wont be an issue as it wont be required. Only filtering will be used