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))),"",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?

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))),"",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?

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 <atyahoo<dotcom
----
"DigitalGM" wrote in message
...
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

Need Assistance: Average/Remove Duplicates
 

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?

Need Assistance: Average/Remove Duplicates
 

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" wrote in message
...
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!

Need Assistance: Average/Remove Duplicates
 

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

"Max" wrote:

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" wrote in message
...
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!