PREVENT DUPLICATION FIND

[NIKHILSP]

PREVENT DUPLICATION FIND K column

RT Request & Film Wrapper (1) (2).xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Draw No:	Sketch No:	Dia:	Thickness:	Weld No:	Spool No:	Welder ID:	Weld Process:	Column1	Column2
2	18.08.2022	T83-CDS-11493-008-SP05	1CA2P01	6''	7.11 mm	SW09 RS	290011-60-03278	B71	GTAW (LTCS)	T83-CDS-11493-008-SP051CA2P0167.11SW09 RS290011-60-03278	T83-CDS-11493-008-SP051CA2P0167.11SW09 RS290011-60-03278
3	18.08.2022	T72-CHS-20393-003-SP03	1CS2W01	16''	9.53 mm	SW08 RS	290011-00-04813	M20+P1	GTAW+GMAW (CS)	T72-CHS-20393-003-SP031CS2W01169.53SW08 RS290011-00-04813	T72-CHS-20393-003-SP031CS2W01169.53SW08 RS290011-00-04813
4	18.08.2022	T83-CDS-11493-008-SP05	1CA2P01	6''	7.11 mm	SW09 RS	290011-60-03278	B71	GTAW (LTCS)	T83-CDS-11493-008-SP051CA2P0167.11SW09 RS290011-60-03278	T83-CDS-11493-008-SP051CA2P0167.11SW09 RS290011-60-03278
RT Request

Cell Formulas
Range	Formula
J2:J4	J2	=CONCATENATE(B2,C2,D2,E2,F2,G2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K:K	Cell Value	duplicates	text	NO
K4	Cell Value	duplicates	text	NO
K1:K3,K5:K1048576	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J2:J4	Custom	=COUNTIF($J$2:$J$3,J2)=1

 

[Joe4]

Welcome to the Board!

This link shows you how you can use Data Validation to prevent duplicate entries in a column.

Prevent Duplicate Entries in Excel

To prevent duplicate values from being entered into Excel, use data validation and the COUNTIF function.

www.excel-easy.com

 

[Andrew Fergus]

Hey Joe
I was watching this thread hoping for a reply. Unfortunately that won't work for the OP given the validation rule is in a cell that concatenates values from 5 other cells. There is no data input directly into the cell that is being tested. I thought I had a solution using helper columns by concatenating the first 4 columns and then determining what the 5th value could not be - data validation is applied to the 5th data input column to avoid duplication of the 5 concatenated values. But then I realised I was only testing the first instance of potential duplication and not all possible instances (I was using semi-fixed ranges with a match function). The issue that needs to be solved for DV is basically running a text concat function inside an array formula which I think cannot be solved using native formulas in Excel 2010. I found a work around but it is very clunky and contains a number of assumptions. Any ideas?
Cheers

 

[Georgiboy]

Perhaps as the cells B:G make up the value of J we could use a Worksheet_Change event to capture the duplicate as it is created. We could then report that to the user and then undo the last action to remove the created duplicate.

Example code below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("B2:G" & Range("A" & Rows.Count).End(xlUp).Row)) Is Nothing Then
        If Application.CountIf(Range("J:J"), Range("J" & Target.Row).Value) > 1 Then
            MsgBox "The last entry created a duplicate in column J"
            Application.Undo
            GoTo jump
        End If
    End If
jump:
    Application.EnableEvents = True
End Sub

 

[Joe4]

Hey Joe
I was watching this thread hoping for a reply. Unfortunately that won't work for the OP given the validation rule is in a cell that concatenates values from 5 other cells. There is no data input directly into the cell that is being tested. I thought I had a solution using helper columns by concatenating the first 4 columns and then determining what the 5th value could not be - data validation is applied to the 5th data input column to avoid duplication of the 5 concatenated values. But then I realised I was only testing the first instance of potential duplication and not all possible instances (I was using semi-fixed ranges with a match function). The issue that needs to be solved for DV is basically running a text concat function inside an array formula which I think cannot be solved using native formulas in Excel 2010. I found a work around but it is very clunky and contains a number of assumptions. Any ideas?
Cheers

Perhaps I am not understanding something about the question, or you are seeing something that I am not (there was not much explanation in the original question).
I see that column J is a concatenation of 5 columns, but they appear to be asking about column K, not J.
I do not see any formulas in column K, so I assumed that column was being hard-coded.
If it is a formula, I would need to see what that formula is.

 

[Georgiboy]

I had assumed that column K was just put there as a value version of J to show the duplicates. I am unsure now.

 

[Joe4]

I had assumed that column K was just put there as a value version of J to show the duplicates. I am unsure now.

That is a possibility, though then I would have expected them to say:

PREVENT DUPLICATION FIND J column

instead of:

PREVENT DUPLICATION FIND K column

That is the problem with vague requests without much explanation. Too much is left up to the person to assume, and people can have different interpretations.