Comparing multiple cells using formulae

Copper Contributor

I am looking for a formula that looks at multiple cells down a column and to say "Error" if 2 or more cells have the same data.

I need it to look at A1, A3, A5, A7, A9, A11 and if in one of those cells it had the same input for example, A1 and A5 both had "ABC", then in A12 I want the words "Error" to pop up. 

Any help greatly appreciated, thanks. 

2 Replies

@SamF1234 

=IF(COUNTA(UNIQUE(FILTER(A1:A11,ISODD(ROW(A1:A11))*NOT(ISBLANK(A1:A11))),,1))<>COUNTA(UNIQUE(FILTER(A1:A11,ISODD(ROW(A1:A11))*NOT(ISBLANK(A1:A11))),,0)),"Error","")

If you have Office 365 or Excel 2021 you can try this formula. FILTER selects cells A1, A3, A5, A7, A9 and A11. Then the number of unique values with duplicates and without duplicates is compared.

comparing multiple cells.png

@SamF1234 

Try this one:

 

data being A1:A11

=IF(MAX(COUNTIF(data,data))>1,"Error","")