Compare not-blank cells in row, flag mismatches

[joshbjames]

I have 6 columns of data that I'd like to check and see if each value in a row are equal. Not every cell in a row has a value and it is a bit tricky for me to come up with a solution.

Here is a bit of sample data, and the kind of flag I'd like to have in column 7.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

A12968F	A12968F
A120E68			A120E68
A121915		A121915
A121914		A121913				Error
A120A56					A120A56
A1245B0	A1245B0
A120A56
A1237DC
					A1245C7
		A1217D1
A123B14	A123B14
A123B33		A123B33	A123B33	A123B33	A123B34	Error
A12A658	A12A658	A12A658	A12A658	A12A658	A12A658

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

 

[Ron Coderre]

With your sample data in A1:F17...this regular formula, copied down, flags records with more than one ref value:

G1: =IF(COUNTIF(A1:F1,INDEX(A1:F1,MATCH("*",A1:F1,0)))=COUNTA(A1:F1),"OK","ERROR")

Is that something you can work with?

 

[joshbjames]

Yeah, it looks like it works great, thanks! I had to convert the formulas to values and replace the blanks for some reason, but it was a great solution. I really appreciate it!

 

[Peter_SSs]

I had to convert the formulas to values and replace the blanks ..

IF ..
- You would prefer to leave the formulas in place & still get the results
- The 'blanks' are formulas returning the null string ""
- You have Excel 2016 through Office 365
.. then you could try this alternative, copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	A12968F	A12968F
2
3	A120E68			A120E68
4	A121915		A121915
5	A121914		A121913				Error
6	A120A56					A120A56
7	A1245B0	A1245B0
8	A120A56
9	A1237DC
10						A1245C7
11
12
13			A1217D1
14
15	A123B14	A123B14
16	A123B33		A123B33	A123B33	A123B33	A123B34	Error
17	A12A658	A12A658	A12A658	A12A658	A12A658	A12A658
Compare

 

[joshbjames]

I'm running into this a bit more, I think I need to upgrade my software. It would have been great to keep my formulas. Thank you for your solution!

 

[Ron Coderre]

Josh
If the contents of the test range will always have 2 or more characters OR formula blanks (=""),
try this:

G1: =IF(COUNTIF(A1:F1,INDEX(A1:F1,MATCH("??*",A1:F1,0)))=COUNTIF(A1:F1,"??*"),"OK","ERROR")

edited to include this enhancement:
Since formula blanks are zero characters....this (slightly shorter formula) also works:

G1: =IF(COUNTIF(A1:F1,INDEX(A1:F1,MATCH("?*",A1:F1,0)))=COUNTIF(A1:F1,"?*"),"","ERROR")

Does that solve your problem?

 

[Peter_SSs]

I'm running into this a bit more, I think I need to upgrade my software.

Sounds like you don't meet the Excel 2016 criteria.

It would have been great to keep my formulas.

OK, what about this version (shorter than my last version anyway )?

Excel Workbook
	A	B	C	D	E	F	G
1	A12968F	A12968F
2
3	A120E68			A120E68
4	A121915		A121915
5	A121914		A121913				Error
6	A120A56					A120A56
7	A1245B0	A1245B0
8	A120A56
9	A1237DC
10						A1245C7
11
12
13			A1217D1
14
15	A123B14	A123B14
16	A123B33		A123B33	A123B33	A123B33	A123B34	Error
17	A12A658	A12A658	A12A658	A12A658	A12A658	A12A658
Compare (2)

Edit: If you might want to insert more columns into the range being checked, then this slight modification.

=IF(COUNTIF(A1:F1,INDEX(A1:F1,MATCH("?*",A1:F1,0)))=COLUMNS(A1:F1)-COUNTBLANK(A1:F1),"","Error")

 

[joshbjames]

Thank you both again! It took me a few days to get back to this project, but the formulas worked great without me needing to convert formulas or blanks. HUGE HELP, thank you!

 

[Peter_SSs]

.. the formulas worked great without me needing to convert formulas or blanks. HUGE HELP, thank you!

That is good to hear. Thanks for letting us know.