Searching a matrix to signal duplicate rows

Greetings everyone!

Background:
I had a spreadsheet sent my way where I need to modify the existing format. See the attached image for general layout. The function of this sheet is to use a matrix to generate part number suffixes for the series of selected options. This is used for expanding normal part number scheme for custom jobs. The spreadsheet grows over time so the solution to my issue needs to be expandable. What I mean by this is that options can be added, and every time a project comes about with a new combination, a row will be added. As you can see, the combinations have been marked with a highlighted yellow cell with an x. I.E. excel row 23 has the part number suffix A21, and has option1, option5, and option9 selected.

Problem:
What I wish to accomplish is having a way to ensure a duplicate combination of options cannot be created for a new part number suffix, or if a duplicate combination is entered, it is signaled somehow. I wish to do this in a way where there is minimal impact to the current format.

I greatly appreciate input!

ExcelCapture.PNG

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

See the attached workbook. The AFTER tab has a new entry in row 51 that duplicates row 50's options. I propose that the part numbers turn red if the options are duplicated. Let me know if there is a better signal that can be integrated.

Hello and welcome to the forum.

This is a bit of a brute force approach but it'll work.

Create a helper column (can be anywhere) using this formula:
=D$1&D2&E$1&E2&F$1&F2&G$1&G2&H$1&H2&I$1&I2&J$1&J2&K$1&K2&L$1&L2&M$1&M2&N$1&N2&O$1&O2&P$1&P2&Q$1&Q2&R$1&R2
I put this in column S starting in row 2.

Highlight C2:C51 > Conditional Formatting > New Rule > Use a formula
=AND(COUNTIF(D2:R2,"x")>0,COUNTIF(S$2:S2,S2)>1)
Format: Red Font > OK > OK

If you want something a little more "in your face", you can make the whole row go red.

To do this, after putting the helper column into column S, you can highlight C2:R51 and use this formula:
=AND(COUNTIF($D2:$R2,"x")>0,COUNTIF($S$2:$S2,$S2)>1)
Format: Red Fill > OK > OK

That is certainly a brute force method, but my initial test of it worked. Thank you!

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.