Compare text values in multiple columns, identify duplicates and ignore cells

[ahmerafzal]

Can someone please help me and provide a formula to compare text values in multiple columns, if same value exists then Column H is duplicate also ignore empty cells

Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(MAX(COUNTIFS(A1:G1,A1:G1))>1,"Duplicate","Ok")

 

[ahmerafzal]

Hi & welcome to MrExcel.
How about

=IF(MAX(COUNTIFS(A1:G1,A1:G1))>1,"Duplicate","Ok")

In this formula empty cells won't ignore, therefore it won't works

 

[Fluff]

That formula does ignore empty cells, are you sure the cells are empty?

 

[ahmerafzal]

In this formula empty cells won't ignore, therefore it won't works

That formula does ignore empty cells, are you sure the cells are empty?

Check this sheet

 

[Fluff]

Those cells are not empty they contain a formula. So why did you say they were empty?

 

[ahmerafzal]

Those cells are not empty they contain a formula. So why did you say they were empty?

Yes I know now, is there a way to resolve this issue ? How to manage this issue

 

[Fluff]

Try

=IF(MAX(COUNTIFS(E1:J1,E1:J1,E1:J1,"><"))>1,"Duplicate","Ok")

 

[ahmerafzal]

Try

=IF(MAX(COUNTIFS(E1:J1,E1:J1,E1:J1,"><"))>1,"Duplicate","Ok")

Thank you very much its work perfectly

 

[ahmerafzal]

Big thanks