Ggtcf

Why do newer 737s use two different styles of split winglets?

Instead of a Universal Basic Income program, why not implement a "Universal Basic Needs" program?

The German vowel “a” changes to the English “i”

Fastest way to pop N items from a large dict

Problem with FindRoot

Employee lack of ownership

Is a party consisting of only a bard, a cleric, and a warlock functional long-term?

How difficult is it to simply disable/disengage the MCAS on Boeing 737 Max 8 & 9 Aircraft?

Why does a Star of David appear at a rally with Francisco Franco?

How do I hide Chekhov's Gun?

Can I use USB data pins as power source

Tikz picture of two mathematical functions

Shortcut for setting origin to vertex

Is there a place to find the pricing for things not mentioned in the PHB? (non-magical)

Recruiter wants very extensive technical details about all of my previous work

Are ETF trackers fundamentally better than individual stocks?

How well should I expect Adam to work?

PTIJ: Who should I vote for? (21st Knesset Edition)

Why does energy conservation give me the wrong answer in this inelastic collision problem?

Converting a variable frequency to TTL HIGH and LOW levels, based on a fixed (possible non-fixed?) frequency

Equivalents to the present tense

Brexit - No Deal Rejection

What's the meaning of a knight fighting a snail in medieval book illustrations?

Are Roman Catholic priests ever addressed as pastor

Excel - Prevent Duplicate Entries

-1

I know this question has been asked before but the provided solutions did not work for my case. I am trying to develop a spreadsheet to track serial numbers of multiple parts. Each part can have 1 or more sub-level parts that will require a serial number as well. i have tried to use Data Validation but can't get it to work properly. I am basically wanting to create a compound primary key using 3 columns, Part Number, Sub-Level, and Serial Number. I am creating a uniqueID field that concatenates either the CustomerPN and SerialNumber fields or SubPN and SerialNumber fields and I tried to use Data Validation on that column but it will not catch duplicates. I would like to use Data Validation if possible because it stops the cell from being updated to a duplicate value. I want to avoid using VBA if I can since no one else in the office can maintain it if I leave the company.

Basically the spreadsheet should take the customer part number and serial number and compare to make sure that that string has not been used before. it should do the same for the sub-level part numbers.

View sample spreadsheet at https://drive.google.com/open?id=1_hwqSi_bzXNC-M-0OsiprYdIuRFuAthk

excel duplicates

share|improve this question

edited Mar 7 at 16:48

Eric Ryan

asked Mar 7 at 15:18

Eric RyanEric Ryan

11

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Yes, please edit your question to provide some sample data, expected outcome and what you've tried so far.
  
  – cybernetic.nomad
  Mar 7 at 15:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It won't 'stop the cell from being updated', but couldn't you just use conditional formatting - > highlight duplicates and then manually change cells that are dupes?
  
  – Emily Alden
  Mar 7 at 17:53
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I could but I would still be worried that the operator wouldn't catch it and then we have a duplicate serial number reach our customer.
  
  – Eric Ryan
  Mar 7 at 18:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Okay. Do you have the power to change the ID scheme? Because you could limit the dupes by making the ID similar to as follows: CU##PN##SP##YW#### . Then the only way you would have dupes is if there was the same Customer ordering the same parts/subparts on the same day. It would at least prevent the error in your example dataset because that would be CU##PN06SP05YW#### and CU##PN08SP05YW####.
  
  – Emily Alden
  Mar 7 at 19:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I could change the scheme but it wouldn't protect the sheet from duplicate serial numbers for 2 reasons. 1. Our customers usually order 5-10 of each part at a time so there would be duplicates 2. The scheme doesn't take serial numbers into account. An operator could easily duplicate a serial number and the ID scheme wouldn't catch it. I am trying to mimic a database's primary key in Excel. i do not want to Access for the same reason i want to avoid VBA. I am looking for a way to use Data Validation with the result of a formula but do not know if you can.
  
  – Eric Ryan
  Mar 8 at 15:10
  

  
  
  
  

add a comment | 

-1

I know this question has been asked before but the provided solutions did not work for my case. I am trying to develop a spreadsheet to track serial numbers of multiple parts. Each part can have 1 or more sub-level parts that will require a serial number as well. i have tried to use Data Validation but can't get it to work properly. I am basically wanting to create a compound primary key using 3 columns, Part Number, Sub-Level, and Serial Number. I am creating a uniqueID field that concatenates either the CustomerPN and SerialNumber fields or SubPN and SerialNumber fields and I tried to use Data Validation on that column but it will not catch duplicates. I would like to use Data Validation if possible because it stops the cell from being updated to a duplicate value. I want to avoid using VBA if I can since no one else in the office can maintain it if I leave the company.

Basically the spreadsheet should take the customer part number and serial number and compare to make sure that that string has not been used before. it should do the same for the sub-level part numbers.

View sample spreadsheet at https://drive.google.com/open?id=1_hwqSi_bzXNC-M-0OsiprYdIuRFuAthk

excel duplicates

share|improve this question

edited Mar 7 at 16:48

Eric Ryan

asked Mar 7 at 15:18

Eric RyanEric Ryan

11

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Yes, please edit your question to provide some sample data, expected outcome and what you've tried so far.
  
  – cybernetic.nomad
  Mar 7 at 15:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It won't 'stop the cell from being updated', but couldn't you just use conditional formatting - > highlight duplicates and then manually change cells that are dupes?
  
  – Emily Alden
  Mar 7 at 17:53
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I could but I would still be worried that the operator wouldn't catch it and then we have a duplicate serial number reach our customer.
  
  – Eric Ryan
  Mar 7 at 18:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Okay. Do you have the power to change the ID scheme? Because you could limit the dupes by making the ID similar to as follows: CU##PN##SP##YW#### . Then the only way you would have dupes is if there was the same Customer ordering the same parts/subparts on the same day. It would at least prevent the error in your example dataset because that would be CU##PN06SP05YW#### and CU##PN08SP05YW####.
  
  – Emily Alden
  Mar 7 at 19:41
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I could change the scheme but it wouldn't protect the sheet from duplicate serial numbers for 2 reasons. 1. Our customers usually order 5-10 of each part at a time so there would be duplicates 2. The scheme doesn't take serial numbers into account. An operator could easily duplicate a serial number and the ID scheme wouldn't catch it. I am trying to mimic a database's primary key in Excel. i do not want to Access for the same reason i want to avoid VBA. I am looking for a way to use Data Validation with the result of a formula but do not know if you can.
  
  – Eric Ryan
  Mar 8 at 15:10
  

  
  
  
  

add a comment | 

I know this question has been asked before but the provided solutions did not work for my case. I am trying to develop a spreadsheet to track serial numbers of multiple parts. Each part can have 1 or more sub-level parts that will require a serial number as well. i have tried to use Data Validation but can't get it to work properly. I am basically wanting to create a compound primary key using 3 columns, Part Number, Sub-Level, and Serial Number. I am creating a uniqueID field that concatenates either the CustomerPN and SerialNumber fields or SubPN and SerialNumber fields and I tried to use Data Validation on that column but it will not catch duplicates. I would like to use Data Validation if possible because it stops the cell from being updated to a duplicate value. I want to avoid using VBA if I can since no one else in the office can maintain it if I leave the company.

Basically the spreadsheet should take the customer part number and serial number and compare to make sure that that string has not been used before. it should do the same for the sub-level part numbers.

View sample spreadsheet at https://drive.google.com/open?id=1_hwqSi_bzXNC-M-0OsiprYdIuRFuAthk

excel duplicates

share|improve this question

edited Mar 7 at 16:48

Eric Ryan

asked Mar 7 at 15:18

Eric RyanEric Ryan

11

share|improve this question

edited Mar 7 at 16:48

Eric Ryan

asked Mar 7 at 15:18

Eric RyanEric Ryan

11

share|improve this question

edited Mar 7 at 16:48

Eric Ryan

asked Mar 7 at 15:18

Eric RyanEric Ryan

11

asked Mar 7 at 15:18

Eric RyanEric Ryan

11

asked Mar 7 at 15:18

Eric RyanEric Ryan

11