Finding duplicates in a column with multiple product #'s

Jamie NullJamie Null ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
04/25/19 Edited 12/09/19

Hi,

Is there a way to find duplicates when a column may contain multiple product numbers which are comma separated?  I was able to get the below formula to flag red when only one event number is duplicated in a one to one ratio, and also have the formula disregard blanks.  Any ideas if there is a way to find duplicates when your data is comma separated?

=IF([Event #]1 = 0, 0, IF(COUNTIF([Event #]:[Event #], [Event #1) > 1, 1))

Example of the data

EVENT #

Event001

Event001, Event003

Event002

Event003, Event 001

Tags:

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You would want to replace the criteria portion of your COUNTIF with a FIND function.

     

    COUNTIFS([Event #]:[Event #], FIND([Event #]1, @cell) > 0)

    thinkspi.com

  • Jamie NullJamie Null ✭✭✭✭✭

    You are a lifesaver!  Thanks Paul!!

Sign In or Register to comment.