Formula to compare multiple rows values based on another column?

[Murph]

I'm trying to figure out if there is a formula that will compare the values
in column E, based on the values in column A. In column A the values are a
persons ID number, and that number can be repeated on multiple rows. The
values in column E are dollar amounts. The dollar amounts for each persons
ID should be the same, but they all aren't. I'm trying to figure out a
formula that compares the dollar amounts in column E for each person's ID
number, and tells me if one of the dollar amounts is not equal. Maybe
have it return some sort of Identifier, so I can just do an autofilter and
see all the ones that don't equal.

 

[Biff]

Hi!

How do you determine which dollar amount is correct?

ID AMT
xx 100
xy 200
xx 102
aa 110
xx 100
xx 102
xx 150

Which amount for xx is correct?

Biff

 

[Murph]

Well, typically there are about 4 or 5 rows for each ID. usually all but 1
will match.
ID AMT
xx 500
xx 500
xx 250
xx 500

the one that doesn't match is the one that is wrong.

 

[Ola]

One way would be to create a Pivottable:
Row Items: ID and Amount
Data Item: ID (count)

Ola Sandstrom


Example:
ID...Amount.... Count of ID
xx...500..........3
.......250..........1
yy...400..........4
.......130..........1

 

[Biff]

Hi!

Assume your data is in the range A3:E100.

In F3 enter this formula and copy down to F100:

=IF(SUMPRODUCT(--(A$3:A$100=A3),--(E$3:E$100=E3))=1,"X","")

This will place an "X" in the adjacent cell with the
amount that doesn't match.

Biff