Formula to compare multiple rows values based on another column?

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.
--
--
Brendan

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

>-----Original Message-----
>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.
>--
>--
>Brendan
>.
>

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.

"Biff" wrote:

> 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
>
> >-----Original Message-----
> >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.
> >--
> >--
> >Brendan
> >.
> >
>

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

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

>-----Original Message-----
>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.
>
>"Biff" wrote:
>
>> 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
>>
>> >-----Original Message-----
>> >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.
>> >--
>> >--
>> >Brendan
>> >.
>> >
>>
>.
>