Need a refence number that locks to a customer?

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.

Sorry, it took a lot longer than I thought to trim the spreadsheet and fill it with dummy values. Data input is entered directly into the spreadsheet because the full spreadsheet has over 120 columns to fill in (they did it this way to have everything they need in one place) so a data entry form can't be used as that is limited to 32 columns as far as I know, unless there's a way to split it into 4 or 5 different data entry forms and have them linked? (only the first 28 columns are filled in initially and the rest are filled in after the products have been tested). There are a few columns that have unique values for a customer company but as long as I know how it works for one column then I should be able to apply it to the other relevant ones.

The ones I mentioned before are titled "Company Customer" and "Report Number". The Report Number column originally had a concatination of a few other columns but that was ditched before I started and replaced with TRN and TR numbers that are entered manually with no validation check. TRN and TR numbers need to be unique to a customer company, but a TRN or TR number can appear on the spreadsheet multiple times if a customer has more than one test item in that same test report (each with their own row). Hope that makes sense.

I think maybe setting up a database in Access might be something for me to consider for next year to make it more efficient but that will take some time to do, so ideally for now I need to find a solution to solve the data duplication in Excel during input.

I've attached a sample workbook

Wow thank you, it works perfectly you're a genius. I showed it to my colleagues and they were really impressed.

They have now asked if I could add a function (Or button in the Report Number column heading) that can autogenerate the next available Report Number.

I think this would work by Trimming "TRN-" from the cells in Report Number column so I'm left with just a Number value, check what the MAX value is in that column and then increment by one, and then add back the "TRN-" to the start of that number and then place it in the next available Cell in column O (Rows 16 and 17 shouldn't have been empty).

However, I have no idea how to get that idea from my head to actually work haha. Any suggestions?

I have just been told TR's have been scrapped, they're internal Technical Reports and shouldn't have been added to the spreadsheet at all by the ones entering the data because that should only be used for customers. So only TRN numbers will be used from now on. Thanks.

Thank you, as always it worked exactly how I wanted it to. Really appreciate all the help you've given me , can't thank you enough