If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add rows to a formula when copying | Jarvi | General Discussion | 2 | August 22nd, 2004 02:15 PM |
sum formula does not tally all values in column | Asleeplessknight | General Discussion | 1 | August 19th, 2004 07:54 PM |
I need to Sum multiple columns by referencing one formula. | GaryZ | Worksheet Functions | 2 | July 3rd, 2004 12:45 PM |
Multiple values in a single cell, and validation | Harlan Grove | Worksheet Functions | 1 | December 5th, 2003 07:58 PM |
How to compare Multiple Rows and Columns to get data | Lee Li [MSFT] | Setting up and Configuration | 0 | September 15th, 2003 09:09 AM |