Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Murph
 
Posts: n/a
Default 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
  #2   Report Post  
Biff
 
Posts: n/a
Default

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
.

  #3   Report Post  
Murph
 
Posts: n/a
Default

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
.


  #4   Report Post  
Ola
 
Posts: n/a
Default

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
  #5   Report Post  
Biff
 
Posts: n/a
Default

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
.


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I write formula to compare two values and pull the resulta. Renee Excel Worksheet Functions 1 February 11th 05 02:31 AM
Formula to return cell contents based on multiple conditions Bill Excel Worksheet Functions 3 January 19th 05 10:59 AM
how do i count values based on multiple criteria sean Excel Worksheet Functions 2 January 7th 05 02:00 AM
Counting rows based on criteria in multiple cells Margaret Excel Discussion (Misc queries) 11 December 3rd 04 12:04 AM
Count rows based on multiple criteria Murph Excel Worksheet Functions 1 October 28th 04 07:13 AM


All times are GMT +1. The time now is 12:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"