Thanks:  0
Likes:  0

1. Here's a puzzle that is vexing me: I have two adjacent columns (let's say Col A and Col B). Each column contains only fixed numerical values. Both columns are 9 rows deep. All 18 cells in the range contain a number.

I wish to search the first column (A1:A9) to find out if any of the values are duplicated. If there are nine unique values (i.e. no duplication) in that range, then I do nothing more. But if there is duplication, I wish to (1) identify the row of the cells of the duplicated values, (2) take the values contained in Column B in those identified rows and obtain their average. I would like the operation to be able to handle as few as two duplications and as many as nine duplications.

EXAMPLE: The cells A2 and A7 both contain the same fixed numerical value. The cell B2 contains the fixed value 13.3. The cell B7 contains the fixed value 12.7. I would like to obtain the average of those two cells (13.0) and paste that number into both B2 and B7. (The 13.0 need not be pasted as a fixed value.)

Thank you for any enlightenment.

2. Hi,

Place in C1 and copy down your list. Adjust the ranges to match your data, but please pay careful attention to the absolute/relative referencing.

=IF(COUNTIF(\$A\$1:\$A\$9,A1)=1,B1,SUMPRODUCT((\$A\$1:\$A\$9=A1)*(\$B\$1:\$B\$9))/COUNTIF(\$A\$1:\$A\$9,A1))

This will mirror column B data, but place the average of any duplicates into their respective col C cells.

Bye,
Jay

3. On 2002-05-02 17:57, Jay Petrulis wrote:
Hi,

Place in C1 and copy down your list. Adjust the ranges to match your data, but please pay careful attention to the absolute/relative referencing.

=IF(COUNTIF(\$A\$1:\$A\$9,A1)=1,B1,SUMPRODUCT((\$A\$1:\$A\$9=A1)*(\$B\$1:\$B\$9))/COUNTIF(\$A\$1:\$A\$9,A1))

This will mirror column B data, but place the average of any duplicates into their respective col C cells.

Bye,
Jay
EDIT: This requires an additional column to use. One advantage is that it doesn't touch the original data. If you need to overwrite the original data, a macro would be required, or the Copy/PasteSpecial/Delete song and dance.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•