2

I have an Excel sheet where 3 columns (A, B, and C) form a unique identifier for each row, but the values in the columns are not unique. For example:

   ABC    REF   001
   ABC    REF   002
   ABC    REF   003
   ABC    DOC   001
   ABC    DOC   002
   ABC    DOC   003

The above rows are valid, because no row has the same combination of values (e.g. there are no two rows with "ABC REF002" even those there are duplicates of columns B and C)

How can I add an Excel validation such that if the user enters a combination of values in columns A, B, and C that matches the combination of another row, a validation error occurs?

2

Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim I As Long, st As String, J As Long, k As Long
    Dim CH As String

    If Intersect(Range("A:C"), Target) Is Nothing Then Exit Sub
    CH = Chr(1)
    I = Target.Row
    J = Cells(Rows.Count, 1).End(xlUp).Row
    st = Cells(I, 1).Text & CH & Cells(I, 2).Text & CH & Cells(I, 3).Text

    For k = 1 To J
        If k <> I Then
            stk = Cells(k, 1).Text & CH & Cells(k, 2).Text & CH & Cells(k, 3).Text
            If st = stk Then
                MsgBox "Combination already in use:" & vbCrLf & stk
                Exit Sub
            End If
        End If
    Next k

End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

  • This macro gives me the following error: "Unable to get the Text property of the Range class". This error occurs on the line with the st declaration This is with Excel 2007 – Scribblemacher Apr 16 '15 at 15:58
  • Very strange..............I don't see this error. – Gary's Student Apr 16 '15 at 16:04
  • 2
    It looks to have been related to the data format in the cells I was testing. Changing all of the columns to text fixed the problem. – Scribblemacher Apr 16 '15 at 18:31
  • 1
    Good job debugging! – Gary's Student Apr 16 '15 at 18:33
  • @Gary : Any idea of why a 'RunTIme Error : 1007 - Applicatiobn Defined or Object Defined Error' throws? I just change the If conditon only as my column count is more. Therefore as 'Intersect(Range("B:F"), Target)'. Will you be able to assist me ? – hirosht Feb 13 '18 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.