Is there a way to do the following with SUMPRODUCT instead of array formulae
(due to the obvious limitation with having to continually hit
ctrl-shift-enter every time a cell entry changes)...

Take this list:
Fred 95
George 78
Tom 87
George 92
Pete 80
Fred 77
Fred 60

And sort it into the multiple instances of scores for each individual like:

Tom George Fred Pete
87 78 95 80
92 77
60

I'd like to do it with cell coding in the cells below each name by looking
for the 1st instance for the 1st cell below Tom, the 2nd for the 2nd cell,
etc., instead of using a macro. I found the following array formula that
does the task...

{=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$ 1:$A$7)),ROW(1:1)),2)}

Using SUMPRUDCT, I keep running into a need to use array math within the
conditions I'm using as arguments, which is invalid cell coding...

Any ideas? I've worked for days on it and am totally stumped...

Add two helper columns next to your column of names/Scores

1st Helper Column
C1: =COUNTIF(A$1:A1,A1) where A1 is the first entry of names and copy down

2nd helper Column
D1: =A1 & " " & C1 where A1 is the name and C1 is the count of the names.

Let's say Column B contains the Scores and Column D contains the
concatenation of the name and the count.

If F1 I have a name (Tom, for example)
F2:
=INDEX($B:$B,MATCH(F$1&" " &ROW()-1,$D:$D,0))

--
HTH,
Barb Reinhardt



"elfregono" wrote:

Is there a way to do the following with SUMPRODUCT instead of array formulae
(due to the obvious limitation with having to continually hit
ctrl-shift-enter every time a cell entry changes)...

Take this list:
Fred 95
George 78
Tom 87
George 92
Pete 80
Fred 77
Fred 60

And sort it into the multiple instances of scores for each individual like:

Tom George Fred Pete
87 78 95 80
92 77
60

I'd like to do it with cell coding in the cells below each name by looking
for the 1st instance for the 1st cell below Tom, the 2nd for the 2nd cell,
etc., instead of using a macro. I found the following array formula that
does the task...

{=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$ 1:$A$7)),ROW(1:1)),2)}

Using SUMPRUDCT, I keep running into a need to use array math within the
conditions I'm using as arguments, which is invalid cell coding...

Any ideas? I've worked for days on it and am totally stumped...