Comparing (>2) values, ignoring blanks

[mr-tom]

I've got an interesting problem, where 5 systems contain lists of companies with IDs, and the companies are supposed to be consistent across companies, but I believe they're not, i.e:
Company ID 123 may be "Tom Ltd" in the first system, "Adam Ltd" in the second system, etc for 5 systems, but the company ID may not appear in all systems

That gives a data table that could look like:

ID. Sys1. Sys2. Sys3. Sys4. Sys5. Outcome
123. Tom. Tom. Match
234. Adam. Adam. Ben. Mismatch
345. John. Match (i.e. the populated cells don't disagree)


It's the outcome that I'm trying to calculate, so what I think it needs to do is to compare non blank cells in the row and flag where inconsistent results are seen

I've had a google, and a search here, but haven't seen the light. As always, sorry if I've missed an existing answer to this issue.

Any bright ideas?

 

[FormR]

Hi, here is one possible option:

Excel 2013/2016
	A	B	C	D	E	F	G
1	ID.	Sys1.	Sys2.	Sys3.	Sys4.	Sys5.	Outcome
2	123	Tom.	Tom.				Match
3	234	Adam.	Adam.	Ben.			Mismatch
4	345	John.					Match
Sheet1

Cell Formulas
Range	Formula
G2	=IF(COUNTIF(B2:F2,B2)=COUNTA(B2:F2),"Match","Mismatch")

 

[mr-tom]

Darn, sorry, when I submitted that question, I'd spaced out the entries in the table - hadn't realise the forum stripped out all my alignment!

It's far from certain that the first system has the company in it, so the count if would need to identify the first populated system rather than running from B2.

I'll edit the post to include a table.

Sorry again

(edit) - I can't work out how to insert a table without writing it in raw HTML.

 

[FormR]

It's far from certain that the first system has the company in it, so the count if would need to identify the first populated system rather than running from B2.

Hi, like this maybe:

Excel 2013/2016
	A	B	C	D	E	F	G
1	ID.	Sys1.	Sys2.	Sys3.	Sys4.	Sys5.	Outcome
2	123		Tom.		Tom.		Match
3	234		Adam.	Ben.	Adam.		Mismatch
4	345	John.					Match
Sheet1

Cell Formulas
Range	Formula
G2	=IF(COUNTIF(B2:F2,INDEX(B2:F2,MATCH(REPT("z",255),B2:F2)))=COUNTA(B2:F2),"Match","Mismatch")

I can't work out how to insert a table without writing it in raw HTML.

See here for a few different ways to tables directly in your replies:

Attachments

 

[mr-tom]

Awesome - thank you so much