Check for non-identical values in a column

[majime01]

Greetings all,

I am in need of a formula/function to look at a column array and identify any cells that have different values. The cell values will vary, but need to be the same. For example A1:A10 should each have a value of 2, but A5 has a value of 3, so it is flagged somehow (True/False maybe?). It would also need to ignore blank cells.

Any ideas?

Much appreciated,

Ken

[JohnTopley]

Please attach a file (not image) so that respondents (a) do not have to type in data and (b) have data with which test any formulas. Ensure the data is representative of your actual "real world" data.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the (remove confidential) data.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[majime01]

John,
File attached.

Thank you,
Ken

[FlameRetired]

majime01

I am confused. The data does not match the description.

Also it might help if you would post a workbook including expected results with a bried explanation of the logic.

Would you post another workbook please?

[majime01]

Dave,
Workbook changed to match description. Sorry for the confusion and I appreciate the interest.
Ken

[FlameRetired]

If I understand correctly this ?

Entered in B1 and filled down.

=NOT(A1=INDEX($A$1:$A$10,MATCH(1,FREQUENCY($A$1:$A$10,$A$1:$A$10),0)))

	A	B
1	2	TRUE
2	2	TRUE
3	2	TRUE
4	2	TRUE
5	3	FALSE
6	2	TRUE
7	2	TRUE
8	2	TRUE
9	2	TRUE
10	2	TRUE

[majime01]

FlameRetired. Thank you, works great if there is a mismatch value...when values are all equal, it throws an #N/A. Any thoughts?
Ken

[FlameRetired]

Try

=IFERROR(NOT(A1=INDEX($A$1:$A$10,MATCH(1,FREQUENCY($A$1:$A$10,$A$1:$A$10),0))),TRUE)

If there is more than 1 mismatch neither formula will work. If there is you will need another formula. Is there any chance that could happen?

[Phuocam]

One way...

=COUNTIF($A$1:$A$10,A1)=1

[FlameRetired]

Another

=MODE($A$1:$A$10)=A1

[majime01]

That worked just as you stated, except when there is more than one error, just as you stated. There is a chance that there could be more than one mismatch. It is very hard to get Excel to do what the human eye can do (scan for errors) but it doesn't hurt to try. It is also hard to make sure users don't make a mistake when inputting values into a spreadsheet.

[FlameRetired]

Did you look at Phuocam's post #9 and my post #10?