I'm a little confused. Can you explain what parts of the array we need to
change to match our sheets/columns?

I thought I had it, but I was wrong.

Thank you!



"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))),"",IN DEX(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,Shee t1!$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 <atyahoo<dotcom
----
"DigitalGM" wrote in message
...
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?