Need a refence number that locks to a customer?

**KEvans** · 10-25-2012, 02:52 PM

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.

**p24leclerc** · 10-25-2012, 08:01 PM

Are you using an entry sheet or are data input directly into the spreadsheet?
Would you be able to supply us with a sample workbook with your actual setup. Make sure there are no sensitive data in it. Just put dummy values. It is just so we can work a solution to suit your actual workbook format.
Regards

**KEvans** · 10-28-2012, 02:50 PM

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

**p24leclerc** · 10-30-2012, 06:58 PM

Here is a macro the will check the entry made in REPORT NUMBER column. It checks if the TRN-TR number has been used before and if so, it checks if it is for the same company. If not, it prompts the error on a message box then erases the TRN_TR number from the active cell. I also programmed message box if it is OK to use the TRN-TR number for both reasons:
1. it is not in use
2. it is used for the same comany

If you think this is too much annoyance for the operator, you can delete this part of the code.
Here is the macro code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Wks As Worksheet
Dim Cie_New As String, Cie_Old As String
Dim FindTRN As Range
'
Application.EnableEvents = False
'
If Target.Column = 15 Then 'proceed witht the following part of the code ONLY if target cell is in REPORT NUMBER column.
'
  Set Wks = Worksheets("Works Orders Results")
  Set FindTRN = Wks.Columns(15).Cells.Find(Target, , xlValues, xlWhole, xlByColumns, xlNext, False)
  If Not FindTRN Is Nothing Then
    Cie_New = Target.Offset(0, -10).Value
    Cie_Old = Cells(FindTRN.Row, 5).Value
    If Cie_New <> Cie_Old Then
      MsgBox "This report number is actually used by customer " & Chr(13) & Cie_Old & Chr(13) _
        & "Please use another report number", vbOKOnly, "REPORT NUMBER IN USAGE"
      Target = "" ' THIS ERASES THE TRN NUMBER IN ACTIVE CELL.
    Else
      MsgBox "OK to use this report number for this customer", vbOKOnly, "REPORT NUMBER OK"
    End If
  Else
    MsgBox "This Report Number has never been used", vbOKOnly, "REPORT NUMBER USAGE"
  End If
End If
Application.EnableEvents = True
End Sub

Hope this help you.

**KEvans** · 10-31-2012, 06:20 AM

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?

**p24leclerc** · 10-31-2012, 12:35 PM

In an earlier post, you mentionned TRN or TR number. Do you really have 2 series of report numbers? If so, do they share the same numeric serial or are they different? Like next TRN number is TRN-10256 and next TR number is TR-3429.
Is it possible to add a sheet to this workbook? It could even be invisible to operator.
Let me know and I'll take a look at this.
Regards

**KEvans** · 10-31-2012, 12:40 PM

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.

**p24leclerc** · 10-31-2012, 04:40 PM

Hi,
I have inserted a button in cell "O1" named "Create new TRN". Here is the macro it calls:

Public Sub Next_TRN()
Dim TRN_No As Integer, Next_No As Integer, C_ell As Range
Application.EnableEvents = False ' Stop the EVENTS procedures
Next_No = 0 'Set variable to zero
For Each C_ell In Range("O2", Cells(Rows.Count, 15).End(xlUp)) 'Define the total range of column "O"
  If InStr(1, C_ell, "TRN", vbTextCompare) <> 0 Then 'Process only cells with TRN in it
    TRN_No = Val(Right(C_ell, Len(C_ell) - 4)) 'Extract TRN number
    If TRN_No > Next_No Then Next_No = TRN_No 'Record the largest TRN number in variable Next_No
  End If
Next
Next_No = Next_No + 1 'Actual largest TRN number is incremented by one
Cells(Rows.Count, 15).End(xlUp).Offset(1, 0) = "TRN-" & Next_No 'TRN number is placed in last cell of column "O"
Application.EnableEvents = True 'Re-enable the EVENTS procedures
End Sub

I put comments on program lines to help you understand what it does.
Regards

**KEvans** · 11-01-2012, 06:16 AM

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

Need a refence number that locks to a customer?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Re: Need a refence number that locks to a customer?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions