Formula to flag duplicate cells, ignoring blanks

[Kenichi]

Hi,

I'd like to create a Duplicate Check column in my spreadsheet, but am wondering how to tweak it to ignore blank cells?

Ex: =IF(E2=H2=K2=N2=Q2=T2,"Duplicate","") will flag duplicates, but it will also flag row 2 as having duplicates if Q2 and T2 are both blank. How can I alter the formula so that it flags duplicates, unless the duplicates are from blank cells?

Thanks!

 

[etaf]

ignore

 

[Aladin Akyurek]

In U2 control+shift+enter, not just enter:

=IF(MAX(FREQUENCY(IF(MOD(COLUMN(E2:T2)-COLUMN(E2),3)=0,IF(1-(E2:T2=""),MATCH(E2:T2,E2:T2,0))),COLUMN(E2:T2)-COLUMN(E2)+1),1)>1,"duplicate","")

 

[SyedUsman]

Try this

=if(e2="","",if((e2&h2&k2&n2&q2)=rept(t2,5),"duplicate","not duplicate"))

 

[Kenichi]

Thanks everyone! I couldn't get the other formula to work properly, but this one did the trick:

In U2 control+shift+enter, not just enter:

=IF(MAX(FREQUENCY(IF(MOD(COLUMN(E2:T2)-COLUMN(E2),3)=0,IF(1-(E2:T2=""),MATCH(E2:T2,E2:T2,0))),COLUMN(E2:T2)-COLUMN(E2)+1),1)>1,"duplicate","")

 

[Aladin Akyurek]

Thanks everyone! I couldn't get the other formula to work properly, but this one did the trick:

You are welcome.