Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: adjacent columns / duplicate values / average

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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