Composite Primary kay in Excel

[bghosh]

Hello Team,

I need to develop a custom primary key in Excel, i.e. based on multiple columns data, it should be a unique combination and none of the cells data can be NULL. For example, data in Column A, B, C, D form an unique pair. So I need to restrict a user to enter duplicate entry for these columns. Individual columns can have duplicate values but combination of Cell A, Cell B, Cell C & Cell D should be unique. How do we achieve this in Excel.

We need to generate a custom validation and if user violates the rule, an error message should be thrown. Please help.

 

[jmacleary]

Hi there and welcome to the forum. You can get what you want with a helper column (E) which has all 4 columns concatenated, and a formula in column F to look for duplicates. Try this:

Book2
	A	B	C	D	E	F
1	PLOAN1	1	SENSUBRIQUE	NEGATIF	PLOAN11SENSUBRIQUENEGATIF
2	PLOAN1	1	SENSUBRIQUE	POSITIF	PLOAN11SENSUBRIQUEPOSITIF
3	PLOAN1	1	SENSUBRIQUE	POSITIF	PLOAN11SENSUBRIQUEPOSITIF	duplicate
Sheet3

Cell Formulas
Range	Formula
E1:E3	E1	=CONCATENATE(A1,B1,C1,D1)
F2:F3	F2	=IF(ISNA(VLOOKUP(E2,E$1:E1,1,FALSE)),"","duplicate")

PS Please have a look at XL2BB - it will enable you to post sample ranges directly into your question, as I have done here.

 

[bghosh]

Hello John,

Thanks for replying. There are some limitations in using this solution. I don't have the liberty of using extra columns. Actually, I have 1st row as column names of table. So I cannot add extra columns in the sheet. I am reading the 1st row as column names for generating the Insert script of the table, So no extra columns allowed. Please help me with a solution which can be applied on existing columns.

Thanks & Regards
Biswajeet Ghosh

 

[jmacleary]

Hello Biswajeet ,

In that case, the only way would be to have a VBA script which looks for a duplicate. Here is one:

Option Explicit

Function IsDuplicate(A As String, B As String, C As String, D As String) As Long
    Dim LookFor As String
    Dim OneRow As Long
    Dim OneValue As String
    Dim LastRow As Long
    LookFor = A + B + C + D
    LastRow = Range("A65536").End(xlUp).Row

    Dim AllRows As New Scripting.Dictionary
    For OneRow = 2 To LastRow
        OneValue = Cells(OneRow, 1) & Cells(OneRow, 2) & Cells(OneRow, 3) & Cells(OneRow, 4)
        If AllRows.exists(OneValue) Then
            MsgBox "Duplicate found in existing data on row " & OneRow
        Else
            AllRows(OneValue) = OneRow 'Record the row number
        End If
    Next
    If AllRows.exists(LookFor) Then
        IsDuplicate = AllRows(LookFor)
    Else
        IsDuplicate = False
    End If

End Function

Sub tst()
    Dim fred As Long
    fred = IsDuplicate("PLOAN1", "1", "SENSUBRIQUE", "POSITIF")
    If fred Then
        MsgBox "Duplicate found in row " & fred
    Else
        MsgBox "Unique"
    End If
End Sub

You can use the function in vba as shown in the tst subroutine, or as a formula.