Find Duplicate and variation

Hi all, I have a sheet with a list of part numbers with revision. When adding a new part number I would like the column to be checked for the same part number already entered above or same part number with a different revision.
I would like the result to be displayed in the next column to the part number and indicated by changing the colour of the cell only.

So in attached example row 235 would be an exact duplicate of row 233 and should should therefore fill 235 COL B RED.
Row 236 is a partial match (everything before the /) and therefore should fill 236 COL B YELLOW.

It would also be acceptable to have the COL B show DUP for an exact duplicate or REV for same entry before the /


Hope this makes sense.


Quick Edit. Not against the removing COL B and simply having the cell with the duplication in it coloured red for exact duplication and yellow for duplication with exception of data beyond the /.

I know there is a duplication function which would work for the exact but require both.


Note I have posted this in Formulas and functions as believe this will be required but not 100% sure.





Many thanks


23042025.JPG

As advised in the yellow banner at the top of this page, providing a sample workbook is a good idea.

HOW TO ATTACH YOUR SAMPLE WORKBOOK: Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

So, while I am waiting for you to provide a sample workbook, please clarify if ALL duplicates should be RED, or just those that appear AFTER the original entry. Same for revisions.

You can mock up manually what you want in the sample workbook.

You provide a sample workbook so that your helpers don't have to replicate your data (amongst other reasons): you have it, so save us time and share it!

I'll have a look.

CF rules for B1:

=AND($A1<>"",COUNTIF($A$1:$A$8,$A1)>1) (for RED)
=AND($A1<>"",SUMPRODUCT((TEXTBEFORE($A$1:$A$8,"/",-1)=TEXTBEFORE($A1,"/",-1))*1)>1) (for YELLOW)

Make sure the rules are in this order, or YELLOW will override RED.

Any questions?

Seen and gone without any acknowledgement ...

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

No - you should continue here.

Provide a more realistic sample file.

So based on what you've said, you would need these rules for B8:

=AND($A8<>"",COUNTIF($A$8:$A$999,$A8)>1) (for RED)
=AND($A8<>"",SUMPRODUCT((TEXTBEFORE($A$8:$A$999,"/",-1)=TEXTBEFORE($A8,"/",-1))*1)>1) (for YELLOW)

You'd then apply these to: $B$8:$B$999.

Youi'd move the rules to the top of your rule list, with red on top and yellow second.

CF rules for B4:

=AND($A4<>"",COUNTIF($A$4:$A$999,$A4)>1)
=AND($A4<>"",SUMPRODUCT((IFERROR(TEXTBEFORE($A$4:$A$999,"/",-1),0)=IFERROR(TEXTBEFORE($A4,"/",-1),0))*1)>1)

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Gone again without any feedback ...

Did it work this time? Are you happy? Any feedback for your helper at all?

OK - it worked in the example file you gave.

No, I downloaded the sheet you provided at 02:43 and when entering any unique part number the cell turned yellow. As per your previous sheet, this should stay white and only turn yellow if the part number prior to the / is the same.

The conditional formatting I already had in place still worked only the cell in COL B was changing to yellow when it should not.

I don't have any more time to spend on it at the momwent - sorry.

A quick guess:

=AND($A4<>"",SUMPRODUCT((IFERROR(TEXTBEFORE($A$4:$A$999,"/",-1),0)=TEXTBEFORE($A4,"/",-1))*1)>1)

If this is right, please tell me and sign off the thread. If it's not, I'm not able to help any more until probably tomorrow.

See post #19.

Are we there yet? Hopefully that final tweak works.

Gone again. Well, I presume that means it works.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.