Collating and calculating summary list

[VBAmature31]

Hi all,

This forum has worked wonder for me so far and I have learned so much from it,

I need help and advice on the best method to carry out the below task:

So I have a list of people in column A and a score(Score is marked 1-3) against that person in column B.

This list will be populated by different people so most names will appear at least a few time son the list.

I need to to find all the entries of each name in column A and add up their score and average it and populate the results with the persons name in another sheet(Summary sheet.)

The amount of names on this list will change constantly as it will be updated regulatory with new names or new scores.

Find sample list attached.

If you guys could help it would be greatly appreciated.

Thanks
KD

 

[Chihiro]

I'd definitely recommend Pivot Table for this.

Set Data Range to Sheet1!$A:$B for Pivot Table.

All you need to do is refresh Pivot table and it will grab all new data.

FYI - To get rid of (blank) in row labels, just over-write the (blank) row label with single space.

 

[Chihiro]

On second thought, better way of getting rid of (blank) and to set range to be dynamic is to set your data as Table.

For data range type Table1 or whatever you named the table as.

 

[VBAmature31]

Hi Chihiro,

That will do just perfect,thanks!....

How do you get the pivot to add the scores and average them?

I tried a pivot at first but it just returned the amount of times an entry was in my list.

regards
KD

 

[Chihiro]

After you drop score to values field. Click on the score and go to value field settings.



Choose Average and then Number Format.

 

[VBAmature31]

chihiro you are an absolute gentle man thank you so much!!