Search code examples
excelfunctionexcel-formula

Excel Duplication verification Function

So I am trying the spreadsheet to inform me if there are accidental duplicates. I will show the image first to make explaining easier.

enter image description here

So what I am trying to do is use a function on the "DUPLICATE VERIFICATION" column that informs me if there is a duplicate.

=IF(AND(D2=D2:D9, E2=E2:E9), "DUP","OK")

I tried the above but it is not working since I think the Function is not reading"D2:D9" and "E2:E9" as individual words.

Does anyone know of a workaround?

I am very open to VBA if that is the best solution.

Although I normally appreciate any ideas including the ones that require modifying the table in order to fit the requirement, for this specific table I prefer not to since the table is already structured and it is a table that many people use and are used to.

Thanks in advance.

Solution

  • Try using COUNTIFS()

    enter image description here

    =IF(COUNTIFS(D$2:D$9,D2,E$2:E$9,E2)>1,"DUP","OK")