Unique List from table

**snoops** · 12-04-2006, 07:48 PM

Hi,
I apologise if this has been asked in the past but I have searched through quite a number of posts and cannot find my answer.

I think if I explain what my data is it might be easier to understand. Each row on my spreadsheet is a football game and I have a number of columns to show the goal scorers, so you could have the name repeated across the columns if they score more than one goal.

What I want to do is look at the cell range covering all the goal scorers (for the whole season) and bring back a list of names with the corresponding number of goals they have scored in the adjacent cell. It is the unique list I am struggling with not the number of goals.

Any help would be much appreciated.

Thank you in advance.

**BenjieLop** · 12-04-2006, 08:02 PM

Originally Posted by snoops

Hi,
I apologise if this has been asked in the past but I have searched through quite a number of posts and cannot find my answer.

I think if I explain what my data is it might be easier to understand. Each row on my spreadsheet is a football game and I have a number of columns to show the goal scorers, so you could have the name repeated across the columns if they score more than one goal.

What I want to do is look at the cell range covering all the goal scorers (for the whole season) and bring back a list of names with the corresponding number of goals they have scored in the adjacent cell. It is the unique list I am struggling with not the number of goals.

Any help would be much appreciated.

Thank you in advance.

ASSUME that

1. your players names are in A1:A100 and
2. corresponding goals are in B1:B100

If you want to determine the total goals scored by "Player A", here is your
formula:

=sumproduct(--(A1:A100="Player A"),--(B1:B100))

Similarly, if you want to determine the goals scored by "Player B", simply
enter "Player B" in the above formula instead of "Player A"

Another option would be to enter the name of the player in, e.g, Cell C1. In
this option, your formula would be

=sumproduct(--(A1:A100=C1),--(B1:B100))

I hope this is what you are looking for.

Regards.

**snoops** · 12-05-2006, 05:53 PM

Hi, many thanks for your reply but it is not quite what I am looking for.

Each line represents a match and for now I have reserved 6 adjacent cells on each row for each teams goal scorers, so my cell range for each match is columns F:Q, in column R I want to bring back all goal scorers for the season with the total number of goals scored per season in the adjacent cell S. So my cell range will be F2:Q46 (but I have named the cell range goal_scorers). The data in column R will not be representative of what is in that row but it will be a unique list of goal scorers for the season.

Thank you, once again.