Prevent duplicate data in columns

Shei Kee · May 22nd 2016

Hello, i am currently doing a project regarding sales.
May i know how do i code in VBA such as whenever new sales is updated, the code will run and make sure that there is no repeated sales?
and there will be a pop up msgbox that says "There is duplicate data at columns X. please retyped."
i will attach my excel file for better understanding of what i am asking.
forum.ozgrid.com/index.php?attachment/69194/

Oeldere · May 22nd 2016

Re: Prevent duplicate data in columns

NON VBA solution.

D2 shows the text wrong input if it match to the given conditions.

See the green cell in the attached file.

D2=iferror(if(match(A2&B2&C2,$I$4:$I$6,0),"wrong input",""),"")

sktneer · May 22nd 2016

Re: Prevent duplicate data in columns

Please find the attached and click the Update button to see if that works as per your requirement.

Shei Kee · May 23rd 2016

Re: Prevent duplicate data in columns

Thanks so much for your help!! it is exactly what i need! :thumbcoo:

Shei Kee · May 23rd 2016

Re: Prevent duplicate data in columns

Quote from sktneer;771202

Please find the attached and click the Update button to see if that works as per your requirement.

Hi, if my template were to be like that, i just have to change the alphabet E to G to A to C?? why it dont works?
[ATTACH=CONFIG]69208[/ATTACH]

sktneer · May 23rd 2016

Re: Prevent duplicate data in columns

Screenshots are not helpful while debugging the issues.
Please upload the sample workbook with the changes you have made in the sheet layout and in the code.

Shei Kee · May 24th 2016

Re: Prevent duplicate data in columns

Quote from sktneer;771240

Screenshots are not helpful while debugging the issues.
Please upload the sample workbook with the changes you have made in the sheet layout and in the code.

how do i upload? [ATTACH=CONFIG]69217[/ATTACH]

Shei Kee · May 24th 2016

Re: Prevent duplicate data in columns

This is the template
forum.ozgrid.com/index.php?attachment/69219/

And what if i put all the data together but i have to check prevent duplicate data for Sales, Deposit and Expenses?
forum.ozgrid.com/index.php?attachment/69220/

sktneer · May 24th 2016

Re: Prevent duplicate data in columns

This the code for Update_Forecast.xlsm.

Code

Sub Updatedata_datavalidation()
Dim x, y
Dim i As Long, lr As Long
Dim dup As Boolean


Application.EnableEvents = False
Application.ScreenUpdating = False
x = Range("E2:G2").Value
lr = Cells(Rows.Count, 1).End(xlUp).Row
y = Range("A4:C" & lr).Value


For i = 1 To UBound(y, 1)
   If y(i, 1) = x(1, 1) And y(i, 2) = x(1, 2) And y(i, 3) = x(1, 3) Then
      dup = True
      Exit For
   End If
Next i
If dup Then
   MsgBox "Duplicate sale figures. Please retype the sale figures...", vbExclamation, "Duplicate Data!"
   Exit Sub
Else
    Range("E2:G2").Copy
    Range("A3:C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A3:C3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Range("E2").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Display More

Shei Kee · May 24th 2016

Re: Prevent duplicate data in columns

Hi, i dont know why this code cant works, it still allow the same data to be updated

Prevent duplicate data in columns

Participate now!

Share