Forum Discussion

SamF1234's avatar
SamF1234
Copper Contributor
Aug 11, 2023

Comparing multiple cells using formulae

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. 

excel
Formulas and Functions

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 11, 2023

    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.

Resources