Duplicate entry

[gta1216]

I know I can use conditional formatting to highlight duplicate entries. If I was typing it, Excel would auto complete, so I know an entry exist. However, most of my input for this specific Excel spreadsheet is copy/paste. Is there anyway to know an entry is duplicate other than using conditional formatting or sorting?

 

[CA_Punit]

There are numerous ways through which you can determine whether the data entry is duplicate or not
An easy way would be

You can use Countif function to know whether the data entered is duplicate.
if Countif returns 2 or more than it would amount to duplicate entry

 

[gta1216]

With the countif function, I would have to enter the range and criteria every time to search. My report have over a thousand rows and over 50 columns of data. Every item in column B have a string of unique alphanumeric value.

I am unable to manually enter the criteria each time to search since the value in column B is very random. I am wondering if there's a way to identify if the same value was entered more than once in column B as soon I paste the value and tab out of the cell.

 

[Peter_SSs]

most of my input for this specific Excel spreadsheet is copy/paste.

That would obliterate any conditional formatting in the pasted cells so that method probably will not work for you.

Is it only column B that you are looking for duplicates in?

Are you only pasting in column B, or a limited number of columns, or might you be pasting into any of the 50+ columns?

Would a vba solution be acceptable?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[gta1216]

I only want to look for duplicates in column B.

I am pasting in column B. For column D, it can be either paste or manually typed.

I am ok with VBA solution.

 

[Peter_SSs]

I am ok with VBA solution.

Try this worksheet change event code with a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Columns("B").FormatConditions.Delete
  With Range("B1", Range("B" & Rows.Count).End(xlUp))
    .FormatConditions.AddUniqueValues
    With .FormatConditions(1)
      .DupeUnique = xlDuplicate
      .Interior.Color = vbCyan
    End With
  End With
End Sub