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?

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?

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?
>
>
>

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