adjacent columns / duplicate values / average

[chopperfoul]

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.

 

[Jay Petrulis]

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

 

[Jay Petrulis]

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.