Compare selling price in 4 colums

[seansr]

I have a spread sheet of some 15000 products and in columns D, E, F, G I have the selling prices of 4 different locations.
They should all be identical
I just want a simple way / formular to show that either everything is the same or there is a difference
however if a site does not sell anything there could be a blank
so within the 4 coulms, they could all be blank up all 4 having the same or different value
I am not bothered about blank cells just compare numbers greater than 0.01
It could be conditional formatting of a value in colum H

 

[chirayu]

see sample

 

[seansr]

see sample

It nearly works,
If there are 1 or 2 colums populated that have the same value it says mismatch if 3 or 4 colums thhe same works perfectly

I enclose the live working file for you to have a look at

Really appriciateyou looking at this
Sean

 

[vletm]

seansr
If You use , instead of ; then modify ...
... this is for row 3 as You see

=IF(AND(IF(D3<>"";D3;MAX(D3:G3))=MAX(D3:G3);IF(E3<>"";E3;MAX(D3:G3))=MAX(D3:G3);IF(F3<>"";F3;MAX(D3:G3))=MAX(D3:G3);IF(G3<>"";G3;MAX(D3:G3))=MAX(D3:G3));IF(MAX(D3:G3)<>0;"Match";"-");"MisMatch")

... and as You've already over 15000 row
... then even I would use something different.

 

[seansr]

seansr
If You use , instead of ; then modify ...
... this is for row 854 as You see

=IF( AND(IF(D854<>"";D854;MAX(D854:G854))=MAX(D854:G854); IF(E854<>"";E854;MAX(D854:G854))=MAX(D854:G854); IF(F854<>"";F854;MAX(D854:G854))=MAX(D854:G854); IF(G854<>"";G854;MAX(D854:G854))=MAX(D854:G854) ); IF(MAX(D854:G854) <> 0;"Match";"_");"MisMatch")

... and as You've already over 15000 row
... then even I would use something different.

That formular will not copy and paste in comes up with an error

 

[vletm]

seansr
test now ...

 

[vletm]

seansr
with file...
with formula

 

[seansr]

seansr
with file...
with formula

Thanks Both Really Appriciate your help

 

[vletm]

seansr
... and same without those 'few' formulas.
Press [ Do It ]-button to refresh H-column

 

[chirayu]

expanded the formula

 

[bosco_yip]

Another formula option

In H2, copied down :

=IF(SUM(D2:G2)=0,"-",IF(SUMPRODUCT((D2:G2<>"")/COUNTIF(D2:G2,D2:G2&""))=1,"Match","Mismatch"))

Regards
Bosco