Excel formula to display discrepancies

Question

Good morning! Last week I posted a ? about copying/pasting the link from one workbook to another to the avoid the copy/paste and that has worked great. The other issue I'm encountering with my staff and is time consuming is to compare values (both alpha/numeric) across the various fields and have Excel populate only the discrepancy(s). Is there an easy formula to determine across rows if values are the same and if not (regardless of alpha/numeric) and if not, display the outlier in another column/cell?

I've tried conditional formatting to display unique values but if the employee did not enter the info in the exact order as another (which they are individualized answers so not possible) the cell would highlight even tho the data was exactly the same but just in a different order.  I don't know if part of the problem is there are multiple values in one cell (by using alt-enter) rather than adding more columns (which would be over 15 and I want to avoid that if possible)

I have John W's Excel Formula book and I'm overwhelmed.

Any help/insight is appreciated.

Thanks in advance,

Karen

Answer 1

A simple example of the data would help us better understand your needs.

best wishes

Answer 2

Here is a scaled down version of what is typical -the Outlier column would be pull the data from the cells that are not consistent across the rows:

Staff A	Staff B	Staff C	Outlier
W00SXXA	W00SSZA	W00SXXA
I10 <br><br><br>E785	I10	I10<br><br><br>E785

Does the data in the cells (specifically the 2nd row) have to have only 1 value in it or can they enter multiple numbers to get the formula to work correctly?

Karen

Answer 3

I wonder if this solves your problem

Please let us know

best wishes

Answer 4

Hi,

Take a comprehensive example.  Show actual data and expected result.