+ Reply to Thread
Results 1 to 4 of 4

Need Assistance: Average/Remove Duplicates

  1. #1
    DigitalGM
    Guest

    Need Assistance: Average/Remove Duplicates

    I've got quite a little puzzle and I'm just too inexperienced to figure this
    out.
    I have 4 columns of data: Date-Agent-Score-Comment. An agent's name shows
    up multiple times within the "Agent" column.
    I need to move the Agent column to a new sheet while removing the duplicate
    listings and average each agent's average score next to their names.
    Also, will the data output be compatible with that nifty Autofilter feature?

  2. #2
    Max
    Guest

    Re: Need Assistance: Average/Remove Duplicates

    One formulas play you could try ..

    > 4 columns of data: Date-Agent-Score-Comment.


    Assume the source table is in Sheet1, cols A to D, data from row2 down

    Put in E2:

    =IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",ROW()))

    Copy E2 down to say, E100,
    to cover the max expected data in the table

    (Leave E1 empty)

    In Sheet2
    ----------
    With headers in A1:B1 : Agent, AvScore

    Put in A2:

    =IF(ISERROR(SMALL(Sheet1!E:E,ROWS($A$1:A1))),"",INDEX(Sheet1!B:B,MATCH(SMALL
    (Sheet1!E:E,ROWS($A$1:A1)),Sheet1!E:E,0)))

    Put in the formula bar for B2, array-enter
    (i.e. press CTRL+SHIFT+ENTER):

    =IF(A2="","",AVERAGE(IF(Sheet1!$B$2:$B$100=A2,Sheet1!$C$2:$C$100)))

    Format B2 as number to 2 d.p. (say)

    Select A2:B2, fill down to B100
    (cover the same range as in Sheet1's col E)

    Sheet2's cols A and B will return the unique list of Agents and their
    corresponding average scores from Sheet1
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DigitalGM" <[email protected]> wrote in message
    news:[email protected]...
    > I've got quite a little puzzle and I'm just too inexperienced to figure

    this
    > out.
    > I have 4 columns of data: Date-Agent-Score-Comment. An agent's name shows
    > up multiple times within the "Agent" column.
    > I need to move the Agent column to a new sheet while removing the

    duplicate
    > listings and average each agent's average score next to their names.
    > Also, will the data output be compatible with that nifty Autofilter

    feature?



  3. #3
    DigitalGM
    Guest

    Re: Need Assistance: Average/Remove Duplicates

    It took me 45 minutes to figure out how to just do what you told me (not your
    fault, never made an array), but you sir are a genius. Thank you very much.
    I'm going to try to disassemble what you did so I can learn from it.
    Hopefully within a month I'll have an idea of what you did.
    Thanks again,
    Edward

    "Max" wrote:

    > One formulas play you could try ..
    >
    > > 4 columns of data: Date-Agent-Score-Comment.

    >
    > Assume the source table is in Sheet1, cols A to D, data from row2 down
    >
    > Put in E2:
    >
    > =IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",ROW()))
    >
    > Copy E2 down to say, E100,
    > to cover the max expected data in the table
    >
    > (Leave E1 empty)
    >
    > In Sheet2
    > ----------
    > With headers in A1:B1 : Agent, AvScore
    >
    > Put in A2:
    >
    > =IF(ISERROR(SMALL(Sheet1!E:E,ROWS($A$1:A1))),"",INDEX(Sheet1!B:B,MATCH(SMALL
    > (Sheet1!E:E,ROWS($A$1:A1)),Sheet1!E:E,0)))
    >
    > Put in the formula bar for B2, array-enter
    > (i.e. press CTRL+SHIFT+ENTER):
    >
    > =IF(A2="","",AVERAGE(IF(Sheet1!$B$2:$B$100=A2,Sheet1!$C$2:$C$100)))
    >
    > Format B2 as number to 2 d.p. (say)
    >
    > Select A2:B2, fill down to B100
    > (cover the same range as in Sheet1's col E)
    >
    > Sheet2's cols A and B will return the unique list of Agents and their
    > corresponding average scores from Sheet1
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > GMT+8, 1° 22' N 103° 45' E
    > xdemechanik <at>yahoo<dot>com
    > ----
    > "DigitalGM" <[email protected]> wrote in message
    > news:[email protected]...
    > > I've got quite a little puzzle and I'm just too inexperienced to figure

    > this
    > > out.
    > > I have 4 columns of data: Date-Agent-Score-Comment. An agent's name shows
    > > up multiple times within the "Agent" column.
    > > I need to move the Agent column to a new sheet while removing the

    > duplicate
    > > listings and average each agent's average score next to their names.
    > > Also, will the data output be compatible with that nifty Autofilter

    > feature?
    >
    >
    >


  4. #4
    Max
    Guest

    Re: Need Assistance: Average/Remove Duplicates

    You're welcome, Edward !
    Glad to know you got it working <g>
    Thanks for the feedback ..
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "DigitalGM" <[email protected]> wrote in message
    news:[email protected]...
    > It took me 45 minutes to figure out how to just do what you told me (not

    your
    > fault, never made an array), but you sir are a genius. Thank you very

    much.
    > I'm going to try to disassemble what you did so I can learn from it.
    > Hopefully within a month I'll have an idea of what you did.
    > Thanks again,
    > Edward




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1