compare multiple cells

linkn00

New Member
Joined
Aug 15, 2019
Messages
12
Is there a way to compare all cells in a row against each other to make sure they are not the same?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,827
Office Version
2016
Platform
Windows
For whole rows (row 1 in the formula) :
=IF(SUMPRODUCT((COUNTIF(1:1,1:1)-1)*(1:1<>""))>0, "Dup's","No Dup's")

If you don't want for whole rows, change 1:1 to required range.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

IF you have Excel 365 with the new Dynamic Array functions, this would be another way. With limited testing this also seems to be significantly faster when dealing with large ranges (like whole rows)

=IF(COUNTA(UNIQUE(FILTER(1:1,1:1<>""),1))=COUNTA(1:1),"No Dup's","Dup's")
 

Forum statistics

Threads
1,089,218
Messages
5,406,920
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top