Compare data in multiple fields while ignoring null values

[BDickson]

I am trying to compare the values in four different columns to determine if all of the non-null values are the same. I was considering using a nested IF-THEN formula but due to the number of permutations I keep getting lost.  Is there a simpler solution?  I have attached a sample file with my data and the table below shows what I hope to achieve.

Client Code	Client Name	Source 1	Source 2	Source 3	Source 4	Desired Result
CST1	Customer 1	20	20	null	null	Valid
CST2	Customer 2	20	null	20	null	Valid
CST3	Customer 3	20	null	null	20	Valid
CST4	Customer 4	20	null	null	null	Valid
CST5	Customer 5	20	null	null	15	Review
CST6	Customer 6	20	20	15	20	Review
CST7	Customer 7	null	null	15	20	Review

Thanks.

Bruce

[JoBen · Accepted solution]

Hi @BDickson, here is a way that you could do it. Hope this helps :)

[BDickson]

Thanks you for the quick response and the solution.

[JoBen]

Okay. You bet!

[w_chivers]

Hi JoBen, thanks for again for sharing your knowledge. 

Could this workflow be amended to compare text values? 

e.g. 

Customer Code	Customer Name	Source 1	Source 2	Source 3	Result
CST1	Customer 1	KFC	[null]	KFC	Valid
CST2	Customer 2	Subway	Sub way	[null]	Check
CST3	Customer 3	McDonalds	[null]	[null]	Valid

Cheers, Will

[JoBen]

Hi @w_chivers, yes, this could be reconfigured to compare text values. Here is my workflow using the example that you have below, which I've also attached for you. Let me know if you have any issues. Cheers!

 

 

[w_chivers]

Amazing!  Thank you, JoBen. 

This is perfect - really appreciate the time you've taken to reply to this.

Thanks again. - Will

[JoBen]

Okay. You bet!