Hi all,

I have just started a new job at a product testing company and have already been tasked with a problem that they have.
They currently have a massive spreadsheet where they keep their records of Customer companies, and test reference numbers for products that need testing.
The issue is that a customer might need numerous products tested under the same test reference number (e.g. TRN-123456) or numerous tests done, so the rows appear like this:

OrderNo | Customer | TestRefNo | TestItem | ......
3011 | Dell | TRN-123456 | T-1026
3012 | Dell | TRN-123456 | T-1027
3013 | Dell | TRN-123456 | T-1028
3014 | Phillips | TRN-123457 | T-101
3015 | Phillips | TRN-123457 | T-102
3016 | Dell | TRN-123458 | T-1050


The problem is that since the spreadsheet is so big and the data is currently being entered manually, the same TRN numbers have accidentally been used for more than one customer.

To prevent this from happening again, is there a way of using Macros or VBA that assigns a new TRN number (Or checks what TRN numbers have already been entered and increments by one?) for a customer that then locks it to that customer so that it can only be used for that customer no matter how many times its entered, or throws back an error if someone tries typing it in for another customer further along the spreadsheet. Sounds like a stupid problem that could be solved by just having the person entering the data check beforehand but that's not reliable.

I've tried using formulas and data validation already with no luck so I'm stumped :/
Any help with be greatly appreciated.
Thanks.