Need Assistance: Average/Remove Duplicates

[Guest]

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?

 

[Max]

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

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?

 

[Guest]

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]

You're welcome, Edward !
Glad to know you got it working <g>
Thanks for the feedback ..

 

[Annemarie]

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]

Can you upload your sample file (.xls only) using a free filehost,
then post a link to it here?

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik

 

[Annemarie]

unfortunatly i cannot. i am working on this spreadsheet at work and that site
is blocked.