Need Assistance: Average/Remove Duplicates

D

DigitalGM

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

Max

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

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 said:
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
Click to expand...
feature?
 
D

DigitalGM

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
 
M

Max

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

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

Annemarie

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